php - Select 2 products from each category in MySQL

Question

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 and 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

Answer 1

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.