CODEWITHSUNDEEP
sqlsql-serverselectadventureworks
porsh01
porsh01

Reputation: 95

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

Screenshot of my output

Upvotes: 3

Views: 2217

Answers (2)

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 2

Mureinik
Mureinik

Reputation: 310993

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

Upvotes: 1

Related Questions