There is a table
id|cat_id|name 1| 1|test1 2| 1|test2 3| 2|test3 4| 3|test4 5| 3|test5 6| 1|test6
You need to make a selection so that it contains no more than 2 products from each category. How to do it?
Here is a link to the DB, for checking https://dl.dropboxusercontent.com/u/8025044/table.sql
I think the easiest way to do this would be using variables:
select * from ( select T.*,@n:=if(@cat=cat_id,@n+1,1) as Num,@cat:=cat_id from test7 T,(select @n:=0,@cat:=0) A order by cat_id ) A where Num<=2
Sorting in an internal subquery must necessarily begin with cat_id, then you can add other fields to it to control which of the two records to show.
The "regular SQL" variant:
select * from test7 where id in(select max(id) from test7 group by cat_id union select min(id) from test7 group by cat_id )
From the limitations of the method: the record with the minimum and maximum ID is always selected from any group.