# SQL Interview Questions – Highest Grade For Each Students

### 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.

## Solution –

SELECT
student_id,
course_id,
FROM (
SELECT
student_id,
course_id,
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.