row_number() over(partition by [ID] order by [score] desc)
row_number方法使用:
表结构:
查询每个ID分数最高的一条数据
select *,row_number() over(partition by [ID] order by [score] desc) as 'Top' from student
结果:
加上筛选:
select * from (
select *,row_number() over(partition by [ID] order by [score] desc) as 'Top' from student
)a where a.Top = 1