SQL Server – Find Highest Sold Item Name for Each Date

sql servert-sql

I have three tables 

table 1: items (int,itemName)
table 2: orders(order id,oDate)
table 3: orderDetails(orderId, itemId, itemQnt)

Here is the complete schema with some demo values

What is the best way to find top sold itemName for each day.
Output would be like date,itemName.

Best Answer

If you want ties:

;WITH od AS
(
  SELECT o.oDate, od.itemId, 
    r = RANK() OVER (PARTITION BY o.oDate ORDER BY SUM(od.itemQnt) DESC)
  FROM dbo.orderDetails AS od
  INNER JOIN dbo.orders AS o
  ON od.orderId = o.orderId
  GROUP BY o.oDate, od.itemId
)
SELECT od.oDate, i.name, r, rn
FROM od INNER JOIN dbo.items AS i
ON od.itemId = i.itemId
WHERE r = 1
ORDER BY od.oDate;

If you don't want ties, change RANK() to ROW_NUMBER(), just note that it will pick an arbitrary winner unless you add some kind of tie-breaker to the ORDER BY.

If you need to fill in gaps in some date range, for example if yesterday had no sales but you still want a row, you can generate a set of dates first, and then use an outer join (this example returns rows from January 1 -> January 15 inclusive):

DECLARE @startDate DATE, @endDate DATE;
SELECT @startDate = '20150101',
       @endDate   = '20150115';

;WITH [days](d) AS
(
  SELECT TOP (DATEDIFF(DAY, @startDate, @endDate)+1)
    DATEADD(DAY, ROW_NUMBER() OVER
    (ORDER BY [object_id])-1, @startDate)
  FROM sys.all_columns
  ORDER BY [object_id]
),
od AS
(
  SELECT o.oDate, od.itemId, 
    r = RANK() OVER (PARTITION BY o.oDate ORDER BY SUM(od.itemQnt) DESC)
  FROM dbo.orderDetails AS od
  INNER JOIN dbo.orders AS o
  ON od.orderId = o.orderId
  GROUP BY o.oDate, od.itemId
)
SELECT oDate = d.d, 
  name = COALESCE(i.name, '<n/a>')
FROM [days] AS d
LEFT OUTER JOIN od
ON d.d = od.oDate
AND od.r = 1
LEFT OUTER JOIN dbo.items AS i
ON od.itemId = i.itemId
ORDER BY d.d;

(Also, do you really think it's productive to make slightly shorter but far more obscure and difficult-to-remember column names like oDate and itemQnt? And why isn't there a unique constraint on orderDetails(orderId, itemId)? Finally, please stay away from regional, ambiguous formats like mm/dd/yyyy.)

Related Question