MySQL – Get the Top N of a Group
Friday, March 26, 2010 11:43
Getting Top N of a group would be very easy if there just wasn’t that ‘LIMIT & IN/ALL/ANY/SOME subquery’ restriction. But there’s always some way around. Thx to Jon Armstrong from A little Noise for the code hint.
MYSQL table 'measurements'
| Id | Freq | Level | DateM
## Gets the last 2 measurements for each frequency
SELECT t1.* , COUNT(t2.Id) AS cnt FROM measurements AS t1
LEFT JOIN measurements AS t2 ON (t1.DateM, t1.Id) <= (t2.DateM, t2.Id) AND t1.Freq = t2.Freq
GROUP BY t1.Id , t1.DateM, t1.Freq HAVING cnt <= 2 ORDER BY t1.Freq, cnt ;
## Format the result as | Freq | Level1 | Level2
SELECT t1.Freq, MIN(t1.Level) as 'Level1', MAX(t1.Level) as 'Level2'
FROM (SELECT t1.* , COUNT(t2.Id) AS cnt FROM measurements AS t1
LEFT JOIN measurements AS t2 ON (t1.DateM, t1.Id) <= (t2.DateM, t2.Id) AND t1.Freq = t2.Freq
GROUP BY t1.Id , t1.DateM, t1.Freq HAVING cnt <= 2 ORDER BY t1.Freq, cnt) as t1 GROUP BY t1.Freq;