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 INPUT student_id course_id grade 1 101 90 1 102 85 1 103 90 2 101 80 2 102 95 3 101 88 Enrollments Table Students grouped with multiple courses & grades 1 2 3 ALGORITHM STEPS 1 Group by Student Partition data by student_id 2 Find MAX Grade Get highest grade per student 3 Handle Ties Pick smallest course_id if tie 4 Order Results Sort by student_id ASC Processing Student 1: c101:90 c102:85 c103:90 Tie at grade 90! Pick course 101 (smaller) Winner: c101, 90 OK FINAL RESULT student_id course_id grade 1 101 90 2 102 95 3 101 88 3 rows returned Ordered by student_id - OK Summary: 1 --> Grade 90, Course 101 2 --> Grade 95, Course 102 3 --> Grade 88, Course 101 Key Insight: Use a subquery with GROUP BY to find MAX(grade) per student, then join back to get the corresponding course_id. For tie-breaking, use MIN(course_id) in the selection or ORDER BY course_id and LIMIT 1 per group. Window functions like ROW_NUMBER() also work efficiently. TutorialsPoint - Highest Grade For Each Student | Optimal Solution

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