Get top n records for each group of grouped results

Here is one way to do this, using UNION ALL (See SQL Fiddle with Demo). This works with two groups, if you have more than two groups, then you would need to specify the group number and add queries 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 a variety of ways to do this, see this article to determine the best route for your situation:

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

Edit:

This might work for you too, it generates a row number for each record. Using an example from the link above this will return only those records with a row number of less than or equal to 2:

select person, `group`, agefrom (   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;

See Demo

In other databases you can do this using ROW_NUMBER. MySQL doesn't support ROW_NUMBER but you can use variables to emulate it:

SELECT    person,    groupname,    ageFROM(    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 T1WHERE rn <= 2

See it working online: sqlfiddle

Edit I just noticed that bluefeet posted a very similar answer: +1 to him. However this answer has two small advantages:

1. It it is a single query. The variables are initialized inside the SELECT statement.
2. It handles ties as described in the question (alphabetical order by name).

So I'll leave it here in case it can help someone.

Try this:

SELECT a.person, a.group, a.age FROM person AS a WHERE (SELECT COUNT(*) FROM person AS b WHERE b.group = a.group AND b.age >= a.age) <= 2 ORDER BY a.group ASC, a.age DESC

DEMO