求思路 分组求最大值

[carl900]

数据:
a,1
a,2
a,3
b,2
b,1


想求出 a.3,b,2的数据

[天-笑]

row_number() 函数可以解决

[开着拖拉机泡妞]

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

[SQL_2008]

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)
*/