I'd like to find the top 3 most ordered products per supplier in mySql. Here's my simplified model and its tables (primary keys are in italics):

Product : product_id, name, supplier_id

Supplier : supplier_id, name

Order_item : order_item_id, product_id

So 1 supplier can have N products, 1 order_item has 1 product.

So far this is what i was able to get :

SELECT count(*), p.name, s.name FROM order_item oi
JOIN product p on oi.product_id = p.product_id
JOIN supplier s on p.supplier_id = s.id
GROUP BY p.product_id, s.id
ORDER BY COUNT(*) DESC
LIMIT 3;

But this gives me the 3 most ordered products among every supplier, not per supplier. In the exemple below, my sql request would give me this :

But I would like a result similar to this (N groups of top 3's):

Thank you.

CodePudding user response：

In MySQL 8 you should be able to:

WITH prodSupCounts AS (
  SELECT ROW_NUMBER() OVER(partition by s.name ORDER BY count(*) DESC) rn, p.name, s.name, count(*) as ct 
  FROM 
    order_item oi
    JOIN product p on oi.product_id = p.product_id
    JOIN supplier s on p.supplier_id = s.id
  GROUP BY p.product_id, s.id
)
SELECT * FROM prodSupCounts WHERE rn <= 3

This should provide a column rn that is an incrementing counter from 1, in order of descending sale count, and the counter starts from 1 per supplier, so saying rn<=3 gives 3 rows per supplier