This article elaborates how to select N records each group:
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
The best solution for this kind of job is:
set @num := 0, @type := '';
select type, variety, price,
@num := if(@type = type, @num + 1, 1) as row_number,
@type := type as dummy
from fruits force index(type)
group by type, price, variety
having row_number <= 2;
But now I also need to restrict the number of groups to M
,how to do this efficiently?