Search code examples
mysqlcorrelated-subquery

mysql - getting top K options per each group

During my experience with MySQL, I encountered in many opportunities a task to find the best (max/min) row per group.

For example in Table:

Grades(Student_ID, Course_ID, Grade)

to find the top course per each student we can use the correlated-subquery:

SELECT Student_ID, Course_ID
FROM Grades g1
WHERE Grade in (SELECT MAX(Grade)
                FROM Grades g2
                WHERE g1.Student_ID = g2.Student_ID)

But - how can I do the same for TOP K courses?

I found this answer from related question, but they it is using variables, which are not a part of classical SQL. Is there any other solution, or I must use variables in this case?

Solution

  • The ranking technique using MySQL @variables is a way to work around the absence of the RANK() or ROWNUM() window functions from that database.

    The recently released fork of MySQL MariaDB 10.2, has RANK() and related functions. MySQL from Oracle, not yet.

    You're not the only one who wishes MySQL didn't need @variables to do this kind of query.