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.
set @num = 0;
set @prev = "";
if(category != @prev, (@prev := category) XOR (@num := 0),
@prev) as ignore1,
@num := @num + 1 as pos
order by category, result) x
where x.pos <= 3
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.