clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591607 fiddles created (45724 in the last week).

create table tbl ( id int not null, date int not null ) ;
 hidden batch(es)


insert into tbl (id, date) values (1, 2016), (1, 2017), (2, 2017), (2, 2017), (3, 2016), (3, 2017), (4, 2018), (5, 2018) ;
8 rows affected
 hidden batch(es)


-- Mguerra Torres SELECT id, Date FROM tbl WHERE id IN (select id from tbl group by id having count(distinct date)>1)
id Date
1 2016
1 2017
3 2016
3 2017
 hidden batch(es)


-- sabin bio 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
Msg 208 Level 16 State 1 Line 2 Invalid object name 'Table1'.
 hidden batch(es)


-- ypercube -- 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 ;
id date
1 2016
1 2017
3 2016
3 2017
id date
1 2016
1 2017
3 2016
3 2017
id date
1 2016
1 2017
3 2016
3 2017
id date
1 2016
1 2017
3 2016
3 2017
id date
1 2016
1 2017
3 2016
3 2017
 hidden batch(es)