Highest Grade For Each Student - Problem

Given a table Enrollments that contains student enrollments and their grades for different courses, write a SQL query to find the highest grade with its corresponding course for each student.

Key Requirements:

  • Find the maximum grade for each student
  • Include the corresponding course_id for that grade
  • In case of a tie (same highest grade), select the course with the smallest course_id
  • Return results ordered by student_id in ascending order

The table structure is:

  • student_id (int): Student identifier
  • course_id (int): Course identifier
  • grade (int): Grade received (never NULL)
  • Primary key: (student_id, course_id)

Table Schema

Enrollments
Column Name Type Description
student_id PK int Student identifier
course_id PK int Course identifier
grade int Grade received by student (never NULL)
Primary Key: (student_id, course_id)
Note: Each row represents a student's enrollment in a course with their grade. A student can enroll in multiple courses.

Input & Output

Example 1 — Basic Case with Tie
Input Table:
student_id course_id grade
1 10 85
1 11 85
1 12 82
2 20 90
2 21 88
Output:
student_id course_id grade
1 10 85
2 20 90
💡 Note:

Student 1 has grades 85, 85, and 82. The highest grade is 85, achieved in both courses 10 and 11. Since there's a tie, we select course 10 (smaller course_id). Student 2's highest grade is 90 in course 20.
Example 2 — Single Course Per Student
Input Table:
student_id course_id grade
1 100 75
2 200 95
3 300 88
Output:
student_id course_id grade
1 100 75
2 200 95
3 300 88
💡 Note:

Each student is enrolled in only one course, so their highest (and only) grade is returned with the corresponding course_id.
Example 3 — Multiple Ties
Input Table:
student_id course_id grade
1 15 90
1 10 90
1 20 90
2 25 85
2 30 85
Output:
student_id course_id grade
1 10 90
2 25 85
💡 Note:

Student 1 has three courses with grade 90. Course 10 is selected as it has the smallest course_id. Student 2 has two courses with grade 85, and course 25 is selected (smaller than 30).

Constraints

  • 1 ≤ student_id ≤ 10^5
  • 1 ≤ course_id ≤ 10^5
  • 0 ≤ grade ≤ 100
  • grade is never NULL
  • (student_id, course_id) is unique (primary key)

Visualization

Tap to expand
Highest Grade For Each Student - SQL ProblemInput: All Enrollmentsstudent_idcourse_idgrade11085111851128222090MAXPer StudentOutput: Best Grade Eachstudent_idcourse_idgrade1108522090Key Logic: Handle TiesStudent 1: Max grade 85 appears in courses 10 and 11→ Tie-breaking: Choose course 10 (smaller course_id)Student 2: Max grade 90 only in course 20→ No tie, select course 20ORDER BY grade DESC, course_id ASC handles ties elegantly
Understanding the Visualization
1
Input
Student enrollments with grades
2
Group & Rank
Partition by student, rank by grade/course
3
Output
Best grade per student
Key Takeaway
🎯 Key Insight: Use ROW_NUMBER() with ORDER BY grade DESC, course_id ASC to handle both maximum selection and tie-breaking in a single window function

Related Problems

Asked in
Facebook 28 Amazon 24 Google 19
23.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen