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;
Advertisement
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;