Retrieve top selling products

Question

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:

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.

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.

Please also read https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms — Reporter, Mar 31 '22 at 10:23
As explained in the [question guide](https://stackoverflow.com/help/how-to-ask), do not post [images](https://meta.stackoverflow.com/questions/285551) of data, sample data should be *consumable text* in your question, ideally as *create* and *insert* statements, or alternatively a [DB<>Fiddle](https://dbfiddle.uk/). — Stu, Mar 31 '22 at 10:24
@Stu. I can not post the whole database here (so I gave a link for it to build it just in case). I can copy text of the tables I am referring to though if that is what you refer to please? — Avv, Mar 31 '22 at 10:34
`CREATE TABLE` and a handful of rows as `INSERT` statements will do — Charlieface, Mar 31 '22 at 11:09

score 1 · Answer 1 · answered Mar 31 '22 at 10:32

1

You should do a
SELECT DISTINCT productid FROM OrderDetails ORDER BY quantity GROUP BY productId LIMIT 5
At least this is the mysql syntax.

answered Mar 31 '22 at 10:32

Big Zed

417
5
11

I will try it. Thanks. Not sure why I got dislike for the question as I can not post the whole database here? – Avv Mar 31 '22 at 10:40
There are best practices to help people who are asking question to be as clear as possible and the readers to easily understand the problem so that they can give proper answers. But don't worry too much about it ! – Big Zed Mar 31 '22 at 10:42
Feedback is about not posing images, but I can post tables thought that might not help as you need the whole database I referred to in the link. – Avv Mar 31 '22 at 10:45
2

To give a good answer, we need to know what RDMS you are referring to (Oracle, MYSQL, SQL Server, Postgres, etc). This is because they don't always use the same syntax. Hope this works for you. Also: Top selling could be more than just quantity, it could also mean the sum of: UnitPrice * Quantity * (1 - Discount) – VvdL Mar 31 '22 at 10:45
1

@VvdL. Thanks. I added SQL Server and also provided a link for the whole database to build in SQL Server. – Avv Mar 31 '22 at 10:54
That's the top 5 largest single orders, not the top 5 products by total sales – Charlieface Mar 31 '22 at 11:10

VvdL · Accepted Answer · 2022-03-31T11:53:54.387

1

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;

edited Mar 31 '22 at 11:53

answered Mar 31 '22 at 10:54

VvdL

2,799
1
3
14

Don't see why it needs a subquery – Charlieface Mar 31 '22 at 11:08
Thanks. That also did not work. I am looking for top 5 in each order (some orders have only 4 products each associated with quantity while others have 20 products with quantity for each product). So, I am looking for top 5 in each order. 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. ' – Avv Mar 31 '22 at 11:42
Ah, I understand after your edits. Please try the revised answer – VvdL Mar 31 '22 at 11:53
1

Thanks. Just got answer. I was going to post the same answer! However, I partitioned only by OrderID, then take desc order of products' quantities as you did. – Avv Mar 31 '22 at 11:53
1

Which is better :) good job, changed it in the answer – VvdL Mar 31 '22 at 11:56

Retrieve top selling products

2 Answers2