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

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

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

Solution

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