Welcome to Subscribe On Youtube

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.

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

All Problems

All Solutions