求sql查询,查询用户同一年份某一课程最高成绩及对应的考试开始结束时间

Question

查询用户同一年份某一课程最高成绩及对应的考试开始结束时间
course_id 为课程

Answer 1

select user_id,batch_id,grade,start_time,end_time
from(
select user_id,batch_id,grade,start_time,end_time,row_number() over(partition by user_id,batch_id order by grade desc) as rnk
from table
where course_id = '2')t
where rnk = 1