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

the following query:

select year, id, rate h year between 2000 , 2009 , id in (select rid table2) group id, year order id, rate desc 

yields:

year    id  rate 2006    p01 8 2003    p01 7.4 2008    p01 6.8 2001    p01 5.9 2007    p01 5.3 2009    p01 4.4 2002    p01 3.9 2004    p01 3.5 2005    p01 2.1 2000    p01 0.8 2001    p02 12.5 2004    p02 12.4 2002    p02 12.2 2003    p02 10.3 2000    p02 8.7 2006    p02 4.6 2007    p02 3.3 

what i'd top 5 results each id:

2006    p01 8 2003    p01 7.4 2008    p01 6.8 2001    p01 5.9 2007    p01 5.3 2001    p02 12.5 2004    p02 12.4 2002    p02 12.2 2003    p02 10.3 2000    p02 8.7 

is there way using kind of limit modifier works within group by?

you use group_concat aggregated function years single column, grouped id , ordered rate:

select   id, group_concat(year order rate desc) grouped_year     yourtable group 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 use find_in_set, returns position of first argument inside 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 combination of group_concat , find_in_set, , filtering position returned find_in_set, use query returns first 5 years every id:

select   yourtable.*   yourtable inner join (     select       id,       group_concat(year order rate desc) grouped_year           yourtable     group id) group_max   on yourtable.id = group_max.id      , find_in_set(year, grouped_year) between 1 , 5 order   yourtable.id, yourtable.year desc; 

please see fiddle here.

please note if more 1 row can have same rate, should consider using group_concat(distinct rate order rate) on rate column instead of year column.

the maximum length of string returned group_concat limited, works if need select few records every group.

wiki