Four items in a recordset group in MySQL

Question

How to fetch some items order by a field in each group, grouped by 'group by'.

like this:

SELECT    (four items in each product_type GROUP) 
FROM      products 
GROUP BY  product_type 
ORDER BY  product_price.

Answer 1

Something like that:

select *
from product p1
where (select count(*) from product p2
         where p2.product_type=p1.product_type and p2.price<p1.price)<4
order by product_type, product_price;

the nested query ranks products by price within it's type (the more other's price is < than this product's price, the greater is it's rank number; The cheapest one has rank 0), the outer query just selects those products with rank between 0 and 3.

Since you wrote "order by", I understand that you need each item, not an aggregate of them. Hence group by is not needed in the outer query. If you need just a string of their names, then go this way:

select
    group_concat(name)

from product p1
where (select count(*) from product p2
         where p2.product_type=p1.product_type and p2.price<p1.price)<4
group by product_type;