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

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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.

Method 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:<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="8ab7cac3">[email protected]</a>+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

Method 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 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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating