So I'm doing this in MySQL.
I have the table students and classes. There is a many-to-many relationship between which I track through the student_class_rel table. Below are the tables and their columns.
student_id | name | email | GPA
class_id | name | room
student_id | class_id
I want to get a student_id of the 5 students for each class with the highest GPAs (along with the class info). How would I do this?
For example, this query gets 100 classes and then gets every
student_id for all students in that class
SELECT *, JSON_ARRAYAGG(student_id) FROM classes INNER JOIN student_class_rel USING(class_id) GROUP BY class_id; LIMIT 100
I want that query, expect the
JSON_ARRAYAGG(student_id) only holds the ID to the top 5 students in terms of GPA for each class rather than every student.
Also, assume there could be upwards of millions of students and hundreds of thousands of classes and that there are around 50 to 1000 students per class. So performance is key for this query.
And I'm aware of hacky type solutions (like make multiple queries, once for each class) but so far those have been WAY too slow. I've spent too much time trying to figure this out, any help is greatly appreciated! (Also let me know if there's any additional info needed).