Find students who take most courses SQL

Assume there’s a table students containing student id, semester id and number of courses taken in each semester:

student    semester    num_courses
10001      23          3
10002      23          5
10003      23          1
10004      25          2
10005      25          3
10003      25          5
10000      26          4
10013      26          2
...

How can I find students in each semester who took the largest number of courses?

Expected output would be:

student    semester    num_courses
10002      23          5
10003      25          5
10000      26          4
...

I thought of using OVER PARTITION BY, but I’m not sure how to use it correctly with this type of query. What I got is not working as expected.

SELECT s.student, s.semester, MAX(s.num_courses) OVER (PARTITION BY s.semester) AS max
FROM students s; 

Answer

Your idea is good. Now that you have the students’ semester course counts along with the maximum semester course counts, compare the two:

SELECT semester, num_courses, student
FROM
(
  SELECT
    student,
    semester, 
    num_courses,
    MAX(num_courses) OVER (PARTITION BY semester) AS semester_max_num_courses
  FROM students s
) with_max
WHERE num_courses = semester_max_num_courses
ORDER BY semester, student;

Another approach would be to select all maximum semester course counts and then use this to get the students:

SELECT semester, num_courses, student
FROM students
WHERE (semester, num_courses) IN
(
  SELECT semester, MAX(num_courses)
  from students
  GROUP BY semester
)
ORDER BY semester, student;