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