Create table scores (id int,stuid varchar(10),courid varchar(10),score int)
insert scores select 1,201601,11,99
insert scores select 2,201601,12,99
insert scores select 3,201601,13,99
insert scores select 4,201601,14,99
insert scores select 5,201602,11,99
insert scores select 6,201602,12,100
insert scores select 7,201602,13,88
insert scores select 8,201602,14,66
insert scores select 9,201603,11,33
insert scores select 10,201603,12,88
insert scores select 11,201603,13,55
insert scores select 12,201603,14,44
insert scores select 13,201604,11,99
select (select COUNT(1) from scores where courid = a.courid and score >=a.score ) as sn,*
into #t from scores a order by courid
select * from #t order by courid ,score desc
select * from #t a where sn in (select top 2 sn from #t where courid = a.courid order by sn)
drop table #t,scores
