select sno,cursorid,grade
from
(
select rn=row_number() over(partition by cursorid order by grade desc )
from stu
) k
where rn between 1 and 2
create table stu
(
sno varchar(2),
cursorid varchar(2),
grade decimal(18,0))
insert stu
select 1,1,99 union all
select 2,1,98 union all
select 3,1,100 union all
select 4,2, 88 union all
select 5,2,87 union all
select 6,2,88 union all
select 7,3,99 union all
select 8,3,98 union all
select 9,3,100
go
select cursorid,sno,grade
from
(
select rn=row_number() over(partition by cursorid order by grade desc ),*
from stu
) k
where rn between 1 and 2
/*
cursorid sno grade
-------- ---- ---------------------------------------
1 3 100
1 1 99
2 4 88
2 6 88
3 9 100
3 7 99
*/
IF object_id('stu')IS NOT NULL DROP TABLE stu
create table stu(
sno varchar(2),
cursorid varchar(2),
grade decimal(18,0))
insert stu
select 1,1,99 union all
select 2,1,98 union all
select 3,1,100 union all
select 4,2, 88 union all
select 5,2,87 union all
select 6,2,88 union all
select 7,3,99 union all
select 8,3,98 union all
select 9,3,100
SELECT * FROM stu
;WITH romance
AS
(
SELECT sno,cursorid,grade,num=row_number()OVER (PARTITION BY cursorid ORDER BY grade desc )
FROM stu
)
SELECT * FROM romance
WHERE num<=2
