Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1112.html
1112. Highest Grade For Each Student
Level
Medium
Description
Table: Enrollments
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| course_id | int |
| grade | int |
+---------------+---------+
(student_id, course_id) is the primary key of this table.
Write a SQL query 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. The output must be sorted by increasing student_id.
The query result format is in the following example:
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 |
+------------+-----------+-------+
Result table:
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 1 | 2 | 99 |
| 2 | 2 | 95 |
| 3 | 3 | 82 |
+------------+-----------+-------+
Solution
For each student, find the highest grade. Then select student, min(course_id) as course_id and grade from table Enrollments using the students' ids and the highest grades selected before. Finally, the result should be grouped by student_id` so that each student’s highest grade is in the result.
# 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) from Enrollments group by student_id
)
group by student_id;