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. 2591575 fiddles created (45740 in the last week).

CREATE TABLE Table1 ([id] int, [dateYear] int) ; INSERT INTO Table1 (id,dateYear) VALUES (1, 2016), (1, 2017), (2, 2017), (2, 2017), (3, 2016), (3, 2017), (4, 2018), (5, 2018) ; SELECT id , dateYear FROM ( SELECT id , dateYear ,RANK() OVER(PARTITION BY id ORDER BY dateYear ASC) AS RK FROM Table1 )A WHERE Exists(SELECT id WHERE RK>1) SELECT T.id,T.dateYear FROM Table1 AS T INNER JOIN ( SELECT id FROM Table1 GROUP BY id HaVING Count(id)>1 AND Count(Distinct dateYear) <> 1 )AS i ON T.ID = i.ID SELECT T.id,T.dateYear FROM Table1 AS T CROSS APPLY (SELECT C.id ,Count(id) as count_id ,Count(Distinct dateYear) 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
id dateYear
1 2017
3 2017
id dateYear
1 2016
1 2017
3 2016
3 2017
id dateYear
1 2016
1 2017
3 2016
3 2017
 hidden batch(es)