Finding rows with same id but different date

Question

Consider below table tbl:

id    date
1     2016
1     2017
2     2017
2     2017
3     2016
3     2017
4     2018
5     2018

How can I get only rows with same id but different date.

Right now I can only get ids with more than 1 count like so:

select id, date
from tbl
group by id
having count(id)>1

How can I only get id 1 and 3 along with date?

Answer 1

Use an AGGREGATE function like MIN or MAX.

If you only want one record per ID, you have to choose which date you want. The first (MIN) or the latest (MAX).

https://learn.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-2017

^^Above information is not what was requested.

Edited per clarification in comments: Just use the HAVING to identify which ID values have more than one date, then apply that to a new SELECT.

SELECT id, Date
FROM tbl
WHERE id IN
    (
        select id 
        from tbl
        group by id
        having count(distinct date)>1
    )

Answer 2

A few more ways to get the same result:

First, using min(date) < max(date) as the HAVING condition - instead of the count(distinct date) > 1. Perhaps slightly more efficient:

select id, date
from tbl
where id in 
      ( select id
        from tbl
        group by id
        having min(date) < max(date)
      ) ;

or with a JOIN:

select t.id, t.date
from tbl as t
     join 
      ( select id
        from tbl
        group by id
        having min(date) < max(date)
      ) as g
      on g.id = t.id ;

and one converting the IN to a correlated EXISTS subquery. Bonus, we can remove the GROUP BY:

select id, date
from tbl as t
where exists
      ( select 1
        from tbl as t2
        where t.id = t2.id
          and t.date <> t2.date
      ) ;

Another that uses window functions:

select id, date
from
  ( select id, date,
           diff_dates = case when min(date) over (partition by id)
                                < max(date) over (partition by id)
                             then 1
                        end 
    from tbl
  ) as d
where diff_dates = 1 ;

and last, one for the obfuscation contest:

select id, date from tbl
except
select id, min(date) from tbl group by id
intersect
select id, max(date) from tbl group by id ;

Test in dbfiffle.

Answer 3

You should count , as you did, and count distinct per date

SELECT T.id,T.date
FROM Table1 AS T
CROSS APPLY
  (SELECT C.id 
          ,Count(id) as count_id
          ,Count(Distinct date) as count_Distinct_Records
      FROM Table1 AS C
      WHERE C.id = T.id
      GROUP BY id) AS CA
WHERE
  CA.count_id > 1
  AND count_Distinct_Records > 1

output here:

id  date
1   2016
1   2017
3   2016
3   2017

dbfiddle

Answer 4

Try this, replace table name

SELECT ROW_NUMBER() OVER(PARTITION BY id ORDER BY id ) RowNum, date
 FROM dbo.table 

Answer 5

select id,date from table2 group by id,date