Skip to content

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

Thinking

Each student keeps the highest grade, breaking ties by the smallest course_id. RANK() OVER (PARTITION BY student_id ORDER BY grade DESC, course_id) encodes that order in one pass; rows with rank \(1\) are the answer, then sort by student_id.

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

Solution 2: Subquery

Thinking

Method 1 needs a window function. Otherwise aggregate MAX(grade) per student, then keep those grades in the base table and take MIN(course_id). The two aggregations implement β€œhighest grade” and β€œsmallest course among ties.”

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

Comments