MySQL selects X-colums for each category_id

Question

I don't know if this is possible in mysql via an ONE query.

Assuming I have a "products" table that has "id", "category_id", "product_name", "price"

Case 1: I want to get 5 products from each category, where the price is more than $ 100

Case 2: I want to get

1- "3 products from category 1"

2- "5 products from category 2"

3- "3 products from category 2, where the price is more than 100 and not selected in paragraph 2 above"

each case in one question, is this possible?

PS: the table has about 100 thousand rows ...

Answer 1

I found this method: it is very fast and gave me exactly what I wanted:

SELECT l.* FROM ( SELECT category, COALESCE( ( SELECT id FROM writings li WHERE li.category= dlo.category ORDER BY li.category, li.id LIMIT 15, 1 ), CAST(0xFFFFFFFF AS DECIMAL)) AS mid FROM ( SELECT id as category FROM cats dl ) dlo ) lo, writings l WHERE l.category>= lo.category AND l.category<= lo.category AND l.id <= lo.mid 

Answer 2

I think you can do this with nested select statements.

http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html

Although, I'm not sure how you will use them as separate select statements after you receive your request. I mean, they will all be one set of records.

Answer 3

Case 2 I think I have ...

  ( select * from products where category_id = 1 limit 3 ) union ( select * from products where category_id = 2 limit 5 ) union ( select * from products where category_id = 2 and price > 100 limit 3 ) 

Note that union will remove duplicates, union all will allow duplication (and therefore faster).

Have you seen?
mySQL Return fives of each category

This is similar to what you are asking ...