create table #t(id1 int,id2 int) insert #t select 1, 2 union all select 2, 2 union all select 3, 2 union all select 1, 3 union all select 3, 3select distinct a.* from #t a , (select id2,id1=max(id1),countid2=count(id1) from #t group by id2) b where a.id2=b.id2 and b.id1<>b.countid2drop table #t/* id1 id2 ----------- ----------- 1 3 3 3*/
create table #t(id1 int,id2 int) insert #t select 1, 2 union all select 2, 2 union all select 3, 2 union all select 1, 3 union all select 3, 3 select * from #t where id2 not in(select id2 from #t group by id2 having count(1)=max(id1))
insert #t
select 1, 2 union all
select 2, 2 union all
select 3, 2 union all
select 1, 3 union all
select 3, 3select distinct a.*
from #t a , (select id2,id1=max(id1),countid2=count(id1) from #t group by id2) b
where a.id2=b.id2 and b.id1<>b.countid2drop table #t/*
id1 id2
----------- -----------
1 3
3 3*/
insert #t
select 1, 2 union all
select 2, 2 union all
select 3, 2 union all
select 1, 3 union all
select 3, 3
select * from #t
where id2 not in(select id2 from #t group by id2 having count(1)=max(id1))