Highest Grade For Each Student Problem


Description

LeetCode Problem 1112.

Table: Enrollments

1
2
3
4
5
6
7
8
+---------------+---------+
| 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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    |
+------------+-----------+-------+


MySQL Solution

1
2
3
4
5
6
7
8
select e1.student_id, min(e1.course_id) as course_id, e1.grade
from Enrollments e1
where e1.grade = 
    (select max(grade) as max_grade
    from Enrollments e2
    where e1.student_id = e2.student_id) 
group by e1.student_id
order by e1.student_id




Related Posts

User Purchase Platform Problem

LeetCode 1127. Write an SQL query to find the total...

Reported Posts II Problem

LeetCode 1132. Write an SQL query to find the average...

Active Business Problem

LeetCode 1126. Write an SQL query to find all active...

Highest Grade For Each Student Problem

LeetCode 1112. Write a SQL query to find the highest...

Reported Posts Problem

LeetCode 1113. Write an SQL query that reports the number...