Question

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.

# 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

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.

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