Choose 2 products from each category in MySQL

Question

Pretty similar to MYSQL - select the first 4 entries for each category in the table , but there is no accepted answer, and one answer there does not, so I ask again.

I have a PRODUCTS table with three columns: ID , NAME and CATEGORY Now I would like to know if it is possible to select 2 products at all for each separate category without executing queries in the PHP loop.

The order of the selected products does not matter, they can also be random. But it’s important that I have only 2 products for each category.

So a good set of results would be

 ID ; NAME ; CATEGORY ::::::::::::::::::::::: 152 ; APPLE ; FRUIT 185 ; ORANGE ; FRUIT 145 ; BEEF ; MEAT 141 ; PORK ; MEAT 410 ; PEPSI ; DRINKS 585 ; CARROT ; VEGETABLES 585 ; TOMATO ; VEGETABLES 

Answer 1

Something in these lines will work:

 SELECT id, name, category FROM ( SELECT *, IF( @prev <> category, @rownum := 1, @rownum := @rownum+1 ) AS rank, @prev := category, @rownum FROM ( SELECT * FROM products ORDER BY category, rand() ) random_prodcts ) products_ranked WHERE rank <= 2; 

He arranges them randomly within categories, then pulls them out, keeping track of how many of them are received from each.

Not sure how well it will scale.

EDIT: Tried this with several thousand records, and it looks fine.

Answer 2

This should do what you are looking for.

 SET @I=0; SET @C=''; SELECT ID, Name, Category FROM ( SELECT B.*, IF(@C != B.Category, @I:=1, @I: =@I +1) AS RowNum, @C:=B.Category FROM ( SELECT ID, Name, Category FROM Products GROUP BY Name, Category ORDER BY Category ) AS B HAVING RowNum <= 2 ) AS A 

Answer 3

FULL SQL:

 SELECT t.id, t.name, t.category FROM the_table AS t , (SELECT MIN(id) AS id_min FROM the_table GROUP BY category) AS t1 , (SELECT MAX(id) AS id_max FROM the_table GROUP BY category) AS t2 WHERE (t.id=t1.id_min) OR (t.id=t2.id_max) 

The query selects the min and max of each category and returns both of them (or only one if it is the same).

You said: "The order of the selected products does not matter, they may be random," so this method should be in order.