MySQL - limiting group bys

Imagine you've got a table with products. Each product has a price and belongs to a specific category. Furthermore, each product also belongs to a sub-category. Now, what if you want to find the cheapest product for each sub-category? That's easy:

开发者_运维问答

SELECT MIN(price), sub_category FROM products GROUP BY sub_category

Right?

Now, imagine you only want to show a maximum of 2 products for each category (not sub-category) for the above result. Is there a way to achieve that in the SQL?

---

Assuming the products have a unique id you may try this:

select * from products p
where p.id in (select p1.id from products p1 
    where p.category = p1.category order by price limit 0,2)
and price = (select min(price) from products p2 
    where p2.sub_category = p.sub_category)

EDIT: @wimvds: Thanks for pointing out the mistake.

EDIT 2: I guess this is still wrong if there is more than one product with a minimal price in a subcategory.

---

Shouldn't it be like this:

select * from products p, products p1 where p.id in (select min(price) from products p2      where p2.sub_category = p2.sub_category) and p1.category=p.category order by p.price limit 0,2