Select 2 products from each category in MySQL

Pretty similar to MYSQL - select first 4 records for each category in a table but there isn't an accepted answer and the one answer there doesn't make much sense so i'm asking again.

I have a PRODUCTS table with 3 columns: ID, NAME a开发者_JAVA百科nd CATEGORY What i would like to know now is if it's at all possible to select 2 products for each distinct category without doing queries in a PHP loop.

The order of the selected products is of no importance, they might as well be random. But it's important that i only have max 2 products per category.

So a good result set 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

Something along 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;

It orders them randomly within the categories, then pulls them out tracking how many it's got from each.

Not sure how nicely it will scale though.

EDIT: Tried it with a few thousand records and it seems ok.

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

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 picks the min and the max of each category and return both of them (or only one if it is the same).

You said "The order of the selected products is of no importance, they might as well be random", so this method should be ok.