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.
sql
# Write your MySQL query statement below WITH T AS ( SELECT *, RANK() OVER ( PARTITION BY student_id ORDER BY grade DESC, course_id ) AS rk FROM Enrollments ) SELECT student_id, course_id, grade FROM T WHERE rk = 1 ORDER BY student_id;(code-box)
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.
sql
# Write your MySQL query statement below SELECT student_id, MIN(course_id) AS course_id, grade FROM Enrollments WHERE (student_id, grade) IN ( SELECT student_id, MAX(grade) AS grade FROM Enrollments GROUP BY 1 ) GROUP BY 1 ORDER BY 1;(code-box)
