Get top n records for each group of grouped results

by

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`, 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;

See Demo

More Related Contents:

  1. SQL select only rows with max value on a column [duplicate]
  2. Using LIMIT within GROUP BY to get N results per group?
  3. ROW_NUMBER() in MySQL
  4. How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
  5. Get records with max value for each group of grouped SQL results
  6. Retrieving the last record in each group – MySQL
  7. Select row with most recent date per user
  8. Get the latest date from grouped MySQL data
  9. In SQL, how to select the top 2 rows for each group
  10. How to get the latest record in each group using GROUP BY? [duplicate]
  11. Get most recent row for given ID
  12. Using ORDER BY and GROUP BY together
  13. How to select the most recent set of dated records from a mysql table
  14. SELECT query return 1 row from each group
  15. Why the order of evaluation for expressions involving user variables is undefined?
  16. How to select data where a field has a min value in MySQL?
  17. How do I limit the number of rows per field value in SQL?
  18. how to delete data from table A besides the customer service and director data in table B?
  19. Using column alias in WHERE clause of MySQL query produces an error
  20. Cannot delete or update a parent row: a foreign key constraint fails
  21. Using an Alias in SQL Calculations
  22. Can MySQL replace multiple characters?
  23. OUTPUT Clause in MySQL
  24. Sorting by date & time in descending order?
  25. MySQL Pivot Table Column Data as Rows
  26. updating columns with a sequence number mysql
  27. MySQL, Concatenate two columns
  28. Select a column with a keyword name
  29. Sum results of a few queries and then find top 5 in SQL
  30. Use Composite Primary Key as Foreign Key

Leave a Comment