select min(id),value from t3 where not exists(select 1 from (select min(id)id,value from t3 group by value)a where a.id=t3.id and a.value=t3.value) group by value
if exists(select 1 from sysobjects where name='tb') drop table tb go create table tb(id int,value varchar(5)) insert into tb select 1, 'AAA' union all select 2, 'AAA' union all select 3, 'AAA' union all select 4, 'BBB' union all select 5, 'BBB' union all select 6, 'BBB';with t as ( select rn=ROW_NUMBER()over(partition by value order by id),* from tb ) select ID,value from t where rn=2ID value 2 AAA 5 BBB
group by value
if exists(select 1 from sysobjects where name='tb')
drop table tb
go
create table tb(id int,value varchar(5))
insert into tb
select 1, 'AAA' union all
select 2, 'AAA' union all
select 3, 'AAA' union all
select 4, 'BBB' union all
select 5, 'BBB' union all
select 6, 'BBB';with t
as
(
select rn=ROW_NUMBER()over(partition by value order by id),*
from tb
)
select ID,value
from t
where rn=2ID value
2 AAA
5 BBB