# $1112. 每位学生的最高成绩
# https://leetcode-cn.com/problems/highest-grade-for-each-student/

# SQL架构
Create table If Not Exists Enrollments (student_id int, course_id int, grade int);
Truncate table Enrollments;
insert into Enrollments (student_id, course_id, grade) values ('2', '2', '95');
insert into Enrollments (student_id, course_id, grade) values ('2', '3', '95');
insert into Enrollments (student_id, course_id, grade) values ('1', '1', '90');
insert into Enrollments (student_id, course_id, grade) values ('1', '2', '99');
insert into Enrollments (student_id, course_id, grade) values ('3', '1', '80');
insert into Enrollments (student_id, course_id, grade) values ('3', '2', '75');
insert into Enrollments (student_id, course_id, grade) values ('3', '3', '82');

# Write your MySQL query statement below
select
    student_id,
    course_id,
    grade
from
    (
        select
            student_id,
            course_id,
            grade,
            rank() over (
                partition by student_id
                order by
                    grade desc,
                    course_id
            ) as ranking
        from
            Enrollments
    ) tmp
where
    ranking = 1
order by
    student_id;

# clean-up
drop table Enrollments;