Here's one way:declare @t table (id int, nr int, [text] varchar(2), val1 varchar(1))insert @tselect 1, 1, 'aa', 'A' union allselect 2, 1, 'bb', 'A' union allselect 3, 2, 'cc', 'B' union allselect 4, 3, 'aa', 'C' union allselect 5, 3, 'ab', 'D' union allselect 6, 4, 'cd', 'E' union allselect 7, 4, 'rf', 'F' union allselect 8, 4, 'rt', 'F' union allselect 9, 4, 'qq', 'G'select t.*from ( select nr from @t group by nr having count(distinct val1) > 1 and count(*) > 1 ) djoin @t t on t.nr = d.nrOUTPUT:id nr text val1----------- ----------- ---- ----4 3 aa C5 3 ab D6 4 cd E7 4 rf F8 4 rt F9 4 qq G
Be One with the OptimizerTG