select * from test t where not exists(select 1 from test m where t.col1=m.col1 and t.col2<m.col2)
或者 select * from test a where a.col2=(select max(col2) from test b where a.col1=b.col2)
或者 select d.* from test a cross apply ( select top (1) col1,col2 from test b where b.col1=a.col1 order by col1,col2 desc ) d group by d.col1,d.col2
create table #tb(col1 varchar(5),col2 int) insert into #tb values('a',1),('a',2),('a',3),('b',2),('b',1);select col1,col2 from (select*,row_number()over(partition by col1 order by col2 desc)rin from #tb)fin where fin.rin=1 drop table #tb /* (5 row(s) affected)col1 col2 ----- ----------- a 3 b 2(2 row(s) affected) */
select * from test t
where not exists(select 1 from test m where t.col1=m.col1 and t.col2<m.col2)
或者
select * from test a
where a.col2=(select max(col2) from test b where a.col1=b.col2)
或者
select d.* from test a
cross apply
(
select top (1) col1,col2 from test b where b.col1=a.col1 order by col1,col2 desc
) d
group by d.col1,d.col2
create table #tb(col1 varchar(5),col2 int)
insert into #tb values('a',1),('a',2),('a',3),('b',2),('b',1);select col1,col2 from
(select*,row_number()over(partition by col1 order by col2 desc)rin from #tb)fin
where fin.rin=1
drop table #tb
/*
(5 row(s) affected)col1 col2
----- -----------
a 3
b 2(2 row(s) affected)
*/