how can i find each course`s student id that has max grade

Question

we need to write a query that brings the student_id `s of all the students that have the highest grades at a course they taking - it will return the best student of each course in other words. Constraint: the query must have a subquery

the data base

at first, I've tried to write it like so

select student_id
from Student s
where s.course_id in
  (select course_id, max(grade) from course c, grade g
   where g.course_id = c.course_id
   group by course_id)

but I don't think it will work because the subquery will just return the max grade from all courses. so I've tried this and I'm not sure if it will work or not.

select student_id
from Student s
where s.course_id in
  (select course_id from grade g1
   where g1.grade = (select max(grade) from grade g where g.course_id = s.course_id)

if anyone could give me any tip for using subqueries I would very much appreciate it

Answer 1

Here is one approach:

select c.course_name, s.student_name, g.grade
from grade g 
inner join course c on c.course_id = g.course_id
inner join student s on s.student_id = g.student_id
where g.grade = (select max(g1.grade) from grade g1 where g1.course_id = g.course_id)

The logic is to join the three tables, and use a correlated subquery to filter on the top grade per course.

This brings the maximum grade for each course, along with the corresponding student. If there are top ties, they all appear in the result.

Of course, if you just want the id of the student and of the course, the joins are not necessary, you can just do:

select g.*
from grade g
where grade = (select max(g1.grade) from grade g1 where g1.course_id = g.course_id)