sqlserver 如何求出分组后每组的第一行

sqlserver 如何求出分组后每组的第一行

SQL2005写法。
例如求出每个部门最高薪酬的人员信息。跟你每组第一行的要求很类似吧！

Declare @Employee table (ID int ,Dept int ,Salary decimal(10,2))
insert into @Employee values(1,10,5500.00)
insert into @Employee values(2,10,4500.00)
insert into @Employee values(3,20,1900.00)
insert into @Employee values(4,20,4800.00)
insert into @Employee values(5,40,6500.00)
insert into @Employee values(6,40,14500.00)
insert into @Employee values(7,40,44500.00)
insert into @Employee values(8,50,6500.00)
insert into @Employee values(9,50,7500.00)
--SELECt * FROM @Employee
SELECt
ID,Dept,Salary
FROM
(
SELECt
ROW_NUMBER() OVER (partition by Dept ORDER BY Salary desc) as Rank,
ID,Dept,Salary
FROM @Employee
) M
WHERe Rank=1
-----
执行结果：
---------
1 10 5500.00
4 20 4800.00
7 40 44500.00
9 50 7500.00

select * from 表名 a where (select count(1) from 表名 where scorename=a.scorename and score>=a.score)<=2 order by scorename,score desc