mysql - SQL select nth member of group

Question

If I have a USER table like

class | age
--------------
1       20    
3       56
2       11
1       12
2       20

Then I can easily get the youngest user in each class via

select class, min(age)
from   user
group by class;

Similarly, by replacing min with max, I can get the oldest. But how can I get the 10th youngest (or oldest) in each class? By the way, I'm using MySql v.5.0.

Cheers,

Answer 1

SELECT a.class,
(
    SELECT b.age 
    FROM users b 
    WHERE b.class = a.class
    ORDER BY age 
    LIMIT 1,1
) as age
FROM users a
GROUP BY a.class

Would get the 2nd youngest in each class. If you wanted the 10th youngest, you'd do LIMIT 9,1 and if you wanted the 10th oldest, you'd do ORDER BY age DESC.