Grab the first n records in each grouped result

Question

<p>The following is the simplest possible example, but any solution should be able to scale to the required n top results: </p> <p>Given the following table, which contains columns for people, groups, and ages, how do you get the 2 oldest people in each group? (Ties within a group should not produce more results, but instead give the top 2 in alphabetical order) </p> <pre> -------- ------- ----- | Person | Group | Age | -------- ------- ----- | Bob | 1 | 32 | |Jill|1|34| | Shawn | 1 | 42 | | Jake | 2 | 29 | | Paul | 2 | 36 | | Laura | 2 | 39 | -------- ------- ----- </pre> <p>Desired result set: </p> <pre> -------- ------- ----- | Shawn | 1 | 42 | |Jill|1|34| | Laura | 2 | 39 | | Paul | 2 | 36 | -------- ------- ----- </pre> <hr>

Got a good MySQL specific answer from @Bohemian: </p> <pre class="brush:php;toolbar:false;">select * from (select * from mytable order by `Group`, Age desc, Person) x group by `Group`</pre> <p>It would be nice to be able to build on this, but I can't see how. </p>

Answer 1

In other databases, you can use ROW_NUMBER to achieve this functionality. MySQL does not support ROW_NUMBER, but you can simulate it using a variable:

SELECT
    person,
    groupname,
    age
FROM
(
    SELECT
        person,
        groupname,
        age,
        @rn := IF(@prev = groupname, @rn + 1, 1) AS rn,
        @prev := groupname
    FROM mytable
    JOIN (SELECT @prev := NULL, @rn := 0) AS vars
    ORDER BY groupname, age DESC, person
) AS T1
WHERE rn <= 2

Online demo: sqlfiddle

---

EDIT I just noticed that bluefeet posted a very similar answer: Give him 1. But this answer has two small advantages:

1. This is a single query. Variables are initialized inside the SELECT statement.
2. It handles the parallel cases described in the question (in alphabetical order by name).

So I'm leaving it in case it helps someone.

Answer 2

Here is one way to do it, using UNION ALL (see SQL Fiddle with demo). This works for two groups, if you have multiple groups you need to specify the group number and add a query for each group:

(
  select *
  from mytable 
  where `group` = 1
  order by age desc
  LIMIT 2
)
UNION ALL
(
  select *
  from mytable 
  where `group` = 2
  order by age desc
  LIMIT 2
)

There are multiple ways to achieve this, please refer to this article to determine the best method for your situation:

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

edit:

This may also work for you, it generates a line number for each record. Using the example from the link above, it will only return records with row numbers less than or equal to 2:

select person, `group`, age
from 
(
   select person, `group`, age,
      (@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number 
  from test t
  CROSS JOIN (select @num:=0, @group:=null) c
  order by `Group`, Age desc, person
) as x 
where x.row_number <= 2;

SeeDemo