Search code examples
sqlsql-serverrdbms

Retrieve top selling products

I would like to receive top 5 selling products in quantity in an order from NorthWind database.

The database has a bunch of tables like Order, OrderDetails, Customers, etc. I was suggested to use Orders Details table below: enter image description here

Now, I tried the following:

WITH cte AS (
    SELECT 
       OrderID,
       Quantity,
       ProductID,
       ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderID) as row_num
    FROM [Order Details] 
)

SELECT * 
FROM cte 
WHERE row_num IN (SELECT row_num FROM cte WHERE row_num <=10)
ORDER BY  OrderID;

Thought this retrieves 10 rows now for each order, they are not ordered based on sold quantities and top sold products are not retrieved properly as for some orders the top sold was beyond the first top 10 rows based on row number I got with ROW_NUMBER() function in SQL.

enter image description here

Edit: For example, if I have 10 orders each with 20 products, then I want top 5 each each product, so the result table should have 50 rows total.

Solution

  • After your edits:

    WITH cte AS (
SELECT 
   OrderID,
   Quantity,
   ProductID,
   ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY Quantity DESC) as row_num
FROM [Order Details] 
)

SELECT * 
FROM cte 
WHERE row_num <= 5
ORDER BY  OrderID;