SQL选择组的第n个成员

如果我有一个像USER这样的表

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

然后我可以轻松地通过每个班级获得最年轻的用户

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

同样,通过用max替换min,我可以得到最老的.但是我怎样才能获得每个班级中最年轻(或最老)的第10名？顺便说一下,我正在使用MySql v.5.0.

干杯,

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

在每堂课中获得第二名最年轻的学生.如果你想要第10个最年轻的,你会这样做LIMIT 9,1,如果你想要第10个最老的,那你就做了ORDER BY age DESC.

这里N有Nth记录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)

它给出了Nth记录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)