In other databases, you can useROW_NUMBERto achieve this functionality. MySQL does not supportROW_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

---

EDITI 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.

Here is one way to do it, usingUNION ALL(seeSQL Fiddlewith demo). This works for two groups, if you have multiple groups you need to specify thegroupnumber and add a query for eachgroup:

( 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