create table student_rank_test(stuID int,grade int,time datetime);
insert into student_rank_test values(1,50,dateadd(day,-1,getdate()));
insert into student_rank_test values(1,70,dateadd(day,-13,getdate()));
insert into student_rank_test values(1,90,dateadd(day,-16,getdate()));
insert into student_rank_test values(2,88,dateadd(day,-31,getdate()));
insert into student_rank_test values(3,65,dateadd(day,-14,getdate()));
insert into student_rank_test values(3,69,dateadd(day,-10,getdate()));
insert into student_rank_test values(4,50,dateadd(day,-22,getdate()));
insert into student_rank_test values(4,77,dateadd(day,-11,getdate()));
insert into student_rank_test values(4,59,dateadd(day,-2,getdate()));
insert into student_rank_test values(5,88,dateadd(day,-21,getdate()));
--
select stuID,grade,rank() over(order by grade desc) rank from
(
select stuID,grade,ROW_NUMBER() over(PARTITION by stuID order by grade desc) as rn
from student_rank_test
) t where t.rn <=1