CODEWITHSUNDEEP
phpmysql
ChrisR
ChrisR

Reputation: 14447

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

Upvotes: 4

Views: 4582

Answers (3)

Simon
Simon

Reputation: 2008

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.

Upvotes: 6

Skrol29
Skrol29

Reputation: 5552

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.

Upvotes: 1

Matt MacLean
Matt MacLean

Reputation: 19648

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

Upvotes: 1

Related Questions