Find top 3 most ordered product per supplier in mysql 8.x

Question

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 :

Count	Product.name	Supplier.name
1450	Strawberry	Good ol'farm
1200	Salmon	Fishing
1150	Shrimp	Fishing

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

Count	Product.name	Supplier.name
1450	Strawberry	Good ol'farm
1000	Orange	Good ol'farm
350	Lemon	Good ol'farm
1200	Salmon	Fishing
950	Carp	Fishing
1150	Shrimp	Fishing

Thank you.

Answer 1

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