Problem Description –
Write a SQL query to find the highest grade with its corresponding course for each student. In case of a tie, you should find the course with the smallest course_id
.
Return the result table ordered by student_id
in ascending order.
The query result format is in the following example.

Difficulty Level – Medium
Problem Link – Highest Grade
Solution –
SELECT
student_id,
course_id,
grade
FROM (
SELECT
student_id,
course_id,
grade,
DENSE_RANK() OVER(PARTITION BY student_id ORDER BY grade DESC, course_id) as rnk
FROM enrollments
) x
WHERE rnk=1
ORDER BY 1
For ranking we are using the DENSE_RANK function. And in the problem it is given that we need to find the highest grade with its corresponding course for each student, so we first partition by student_id then order the grade in descending order. In the question it is also given that in case of tie we need to find the course with the smallest course_i
, so we also added a second order by condition by course_id. Then in the outer query we only selected the rows where rank is 1 to get the highest grades.