Using LIMIT within GROUP BY to get N results per group?

by

You could use GROUP_CONCAT aggregated function to get all years into a single column, grouped by id and ordered by rate:

SELECT   id, GROUP_CONCAT(year ORDER BY rate DESC) grouped_year
FROM     yourtable
GROUP BY id

Result:

-----------------------------------------------------------
|  ID | GROUPED_YEAR                                      |
-----------------------------------------------------------
| p01 | 2006,2003,2008,2001,2007,2009,2002,2004,2005,2000 |
| p02 | 2001,2004,2002,2003,2000,2006,2007                |
-----------------------------------------------------------

And then you could use FIND_IN_SET, that returns the position of the first argument inside the second one, eg.

SELECT FIND_IN_SET('2006', '2006,2003,2008,2001,2007,2009,2002,2004,2005,2000');
1

SELECT FIND_IN_SET('2009', '2006,2003,2008,2001,2007,2009,2002,2004,2005,2000');
6

Using a combination of GROUP_CONCAT and FIND_IN_SET, and filtering by the position returned by find_in_set, you could then use this query that returns only the first 5 years for every id:

SELECT
  yourtable.*
FROM
  yourtable INNER JOIN (
    SELECT
      id,
      GROUP_CONCAT(year ORDER BY rate DESC) grouped_year
    FROM
      yourtable
    GROUP BY id) group_max
  ON yourtable.id = group_max.id
     AND FIND_IN_SET(year, grouped_year) BETWEEN 1 AND 5
ORDER BY
  yourtable.id, yourtable.year DESC;

Please see fiddle here.

Please note that if more than one row can have the same rate, you should consider using GROUP_CONCAT(DISTINCT rate ORDER BY rate) on the rate column instead of the year column.

The maximum length of the string returned by GROUP_CONCAT is limited, so this works well if you need to select a few records for every group.

More Related Contents:

  1. Retrieving the last record in each group – MySQL
  2. In SQL, how to select the top 2 rows for each group
  3. Using ORDER BY and GROUP BY together
  4. SQL select only rows with max value on a column [duplicate]
  5. ROW_NUMBER() in MySQL
  6. Get top n records for each group of grouped results
  7. How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
  8. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  9. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  10. Select row with most recent date per user
  11. Mysql query to dynamically convert rows to columns
  12. MySQL “Group By” and “Order By”
  13. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  14. Get the latest date from grouped MySQL data
  15. How to get the latest record in each group using GROUP BY? [duplicate]
  16. SQL Query to get column values that correspond with MAX value of another column?
  17. MySQL Group By and Sum total value of other column
  18. Get most recent row for given ID
  19. Is it possible to GROUP BY multiple columns using MySQL?
  20. What does SQL clause “GROUP BY 1” mean?
  21. How to select the most recent set of dated records from a mysql table
  22. MySQL: How do I join same table multiple times?
  23. MySQL GROUP BY DateTime +/- 3 seconds
  24. Sum results of a few queries and then find top 5 in SQL
  25. SELECT query return 1 row from each group
  26. How to select data where a field has a min value in MySQL?
  27. MySQL Group By Hours
  28. MySQL update statement to store ranking positions
  29. How do I limit the number of rows per field value in SQL?
  30. SQL query with avg and group by

Leave a Comment