views:

56

answers:

0

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?