mysql select top 1 from each group code example

Example: mysql select top n rows per group

CREATE TABLE yourtable (
  year int,
  id varchar(20),
  rate decimal(10,2)
);

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) <=5
ORDER BY yourtable.id, yourtable.year DESC;

Tags:

Sql Example

Related