January 6

MySQL – Get top N rows for each group

Following table contains results from athletic 10K race:

It contains following fields:

id – autoincrement

full_name – name of participant

category – can be “Junior”, “Senior” and “35+”

result – finish time in seconds

Goal is to extract top 3 participants for each category using SQL query. Microsoft SQL Server has OVER(PARTITION BY fieldname) clause which can help us achieve this goal. However, MySQL doesn’t have any built-in feature. In this case we will utilize variables in order to calculate position for each group.

Inner query does following:

– orders participants by category and result

– assigns position number by category for each participant

Outer query selects only first three participants from each group.

Final result set is:

This query looks little bit dirty but it can save you doing additional processing at application level.