1112. Highest Grade For Each Student π
Description
Table: Enrollments
+---------------+---------+ | Column Name | Type | +---------------+---------+ | student_id | int | | course_id | int | | grade | int | +---------------+---------+ (student_id, course_id) is the primary key (combination of columns with unique values) of this table. grade is never NULL.
Write a solution 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 result format is in the following example.
Example 1:
Input: Enrollments table: +------------+-------------------+ | student_id | course_id | grade | +------------+-----------+-------+ | 2 | 2 | 95 | | 2 | 3 | 95 | | 1 | 1 | 90 | | 1 | 2 | 99 | | 3 | 1 | 80 | | 3 | 2 | 75 | | 3 | 3 | 82 | +------------+-----------+-------+ Output: +------------+-------------------+ | student_id | course_id | grade | +------------+-----------+-------+ | 1 | 2 | 99 | | 2 | 2 | 95 | | 3 | 3 | 82 | +------------+-----------+-------+
Solutions
Solution 1: RANK() OVER() Window Function
We can use the RANK() OVER()
window function to sort the grades of each student in descending order. If the grades are the same, we sort them in ascending order by course number, and then select the record with a rank of $1$ for each student.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
|
Solution 2: Subquery
We can first query the highest grade of each student, and then query the minimum course number corresponding to the highest grade of each student.
1 2 3 4 5 6 7 8 9 10 11 |
|