Find product id of the top selling product of each day, using total sold quantity to determine the top selling product

Question

Adventureworks2008R2 database.

The result I want is each day the MaxQantity sold, for example, OrderDate 2007-09-01 shall have the max quantity of 96 only, but my query gives me 3 different results from the same day, maybe because it is considering the timestamp as well

SELECT DISTINCT CAST(oh.OrderDate AS DATE) OrderDate, (od.ProductID),SUM(od.OrderQty) MAXOrderQty
FROM Sales.SalesOrderDetail od
Inner Join Sales.SalesOrderHeader oh
ON od.SalesOrderID = oh.SalesOrderID
GROUP BY od.ProductID, CAST(oh.OrderDate AS DATE), od.OrderQty
ORDER BY SUM(od.OrderQty) DESC

You're query gives you 3 results as you have 3 products. Remove `ProductID` from your `SELECT` and `GROUP BY`. You also don't need the `DISTINCT`. — Thom A, Jun 23 '18 at 21:12

score 2 · Accepted Answer · answered Jun 23 '18 at 21:25

You can write CTE and self JOIN on MAX Qty by date

;WITH CTE(OrderDate,ProductID,MAXOrderQty) AS(
    SELECT  CAST(oh.OrderDate AS DATE) OrderDate,od.ProductID,SUM(od.OrderQty) MAXOrderQty
    FROM Sales.SalesOrderDetail od
    Inner Join Sales.SalesOrderHeader oh
    ON od.SalesOrderID = oh.SalesOrderID
    GROUP BY od.ProductID, CAST(oh.OrderDate AS DATE)
)
SELECT t1.* 
FROM CTE t1 INNER JOIN (
    select OrderDate,MAX(MAXOrderQty) 'MAXOrderQty'
    from CTE 
    GROUP BY OrderDate
)t2 on t1.OrderDate = t2.OrderDate and t1.MAXOrderQty = t2.MAXOrderQty

score 1 · Answer 2 · answered Jun 23 '18 at 21:10

It's much easier addressing problems like this with window functions. In this case, rank should do the trick:

SELECT OrderDate, ProductID, MaxOrderQty
FROM   (SELECT OrderDate, ProductID, MaxOrderQty,
               RANK() OVER (PARTITION BY OrderDate ORDER BY MaxOrderQty DESC) AS rk
        FROM   Sales.SalesOrderDetail) s
WHERE  rk = 1

Find product id of the top selling product of each day, using total sold quantity to determine the top selling product

2 Answers2