分类排序

create table #result(subject varchar(20),student varchar(20) ,grade int )

insert #result values ('math' ,'jimmy' ,'80')
insert #result values ('math' ,'terry' ,'75')
insert #result values ('math' ,'dylan' ,'85')
insert #result values ('math' ,'daisy' ,'62')
insert #result values ('math' ,'cala' ,'59')

insert #result values ('english' ,'jimmy' ,'80')
insert #result values ('english' ,'terry' ,'75')
insert #result values ('english' ,'dylan' ,'85')
insert #result values ('english' ,'daisy' ,'62')
insert #result values ('english' ,'cala' ,'59')

select * from #result

select subject ,student ,grade ,(select count(*)+1  from #result b where b.subject = a.subject and b.grade > a.grade) as sort
from #result a
order by  subject ,grade desc