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.

Answer 2

Here N presents Nth record oldest

SELECT * FROM users k WHERE N = (SELECT              COUNT( DISTINCT age)            FROM users u            WHERE k.age >= u.age                AND k.class = u.class            GROUP BY u.class) 

and it gives Nth record youngest

SELECT * FROM users k WHERE N = (SELECT              COUNT(DISTINCT age)            FROM users u            WHERE k.age <= u.age                AND k.class = u.class            GROUP BY u.class)