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

It's pretty easy in SQL Server:

select 
  *
from(
   select 
      *,
      row_number() over(order by age asc) as eldest
   from class order by age asc) a
where a.eldest = 10

Following that pattern, for MySQL, I think you'd want to look at this: http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/

Answer 2

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 3

SELECT userid,
class,
age,
( SELECT COUNT(1) FROM user
WHERE class = c.class AND age > u.age
) AS oldercount
FROM user AS u
WHERE oldercount = 9 GROUP BY class

or

SELECT userid,
class,
age
FROM user AS u
WHERE (SELECT COUNT(1) FROM class WHERE class = c.class AND age > u.age) = 9
GROUP BY class

Answer 4

Which version for MySql? You may need to use a Cursor to grab that. But, I'm not sure if AviewAnew's answer would work.

Regards, Frank

Answer 5

The only sql independent way (even if you don't have subqueries mysql <5)

 select  u1.class, u1.age, count(*)  from      user u1 join user u2 
 on u1.class = u2.class and u1.age >= u2.age
 group by u1.class, u1.age
 having count(*) = [number]

gets you the [number] oldest per class

 select  u1.class, u1.age, count(*)  from      user u1 join user u2 
 on u1.class = u2.class and u1.age <= u2.age
 group by u1.class, u1.age
 having count(*) = [number]

gets you the [number] youngest per class

If two people have the same age it may not work as both are returned. If you want to only return one of them you will need a unique key and the query is more complicated.

Answer 6

Any answer that joins a table on it self will create a square law...

- a JOIN b ON a.class = b.class AND a.age >= b.age  
- on average the >= condition will be true for half the class  

- 6 people in a class  
->6*6/2 = 18

- 10 people in a class
->10*10/2 = 50

-> very rapid growth

As the table sizes grow the performance will degrade rapidly. If you're keeping things small and they won't grow much, is it an issue? Your call there...

An alternative involves more code, but grows linearly...

• First, insert all the records intoa new table, with an IDENTITY field, ordered by Class then Age
• Now, for each class, find the MIN(id)
• Now, for each class, rinf the record where is = MIN(id) + 8 (for the 9th eldest)

There are a lot of ways of doing the last 2 steps. I personally would use...

SELECT
    [USER_WITH_IDS].id,
    [USER_WITH_IDS].class,
    [USER_WITH_IDS].age
FROM
    [USER_WITH_IDS]
WHERE
    [USER_WITH_IDS].id = (
                          SELECT
                              MIN([min].ID) + 8
                          FROM
                              [USER_WITH_IDS] AS [min]
                          WHERE
                              [min].class = [USER_WITH_IDS].class
                         )

What this gives is...

• One pass to create the new IDs
• One pass to get the MIN(id) for each class

• One pass to get the records you need

• And depending on how good the optimiser is, using an index (class then id) would allow it to combine the last 2 passes into 1 pass.

2 or 3 passes, no matter how big the table or class size gets. Linear, not square law...