Rating table for one column when sorting by another

Question

I have a subset of a SQL Server 2008 R2 table as follows:

cust_id | prod_id | day | price --------+---------+-----+------- 137656 194528 42373 9.11 137656 194528 42374 9.11 137656 194528 42375 9.61 137656 194528 42376 9.61 137656 194528 42377 9.11 137656 194528 42378 9.11 

I need to rank different price periods as follows:

 cust_id | prod_id | day | price | rank --------+---------+-----+-------+------ 137656 194528 42373 9.11 1 137656 194528 42374 9.11 1 137656 194528 42375 9.61 2 137656 194528 42376 9.61 2 137656 194528 42377 9.11 3 137656 194528 42378 9.11 3 

so that he sorts by cust_id , prod_id and day in ascending order, but increases the rank when the price changes. I tried using DENSE_RANK() as follows:

 SELECT cust_id, prod_id, [day], price, DENSE_RANK() OVER (ORDER BY cust_id, prod_id, price) FROM @prices 

This returns something like:

 cust_id | prod_id | day | price | rank --------+---------+-----+-------+------ 137656 194528 42373 9.11 1 137656 194528 42374 9.11 1 137656 194528 42377 9.11 1 137656 194528 42378 9.11 1 137656 194528 42375 9.61 2 137656 194528 42376 9.61 2 

Obviously, excluding the day from sorting will give me these results, but whenever I include the day in the order under the DENSE_RANK() section, it just breaks each new day into a new identifier ....

Does anyone have any ideas on how this should work? Please rate any advice and can provide more information if required.

Answer 1

The first option with LAG and SUM

 SELECT *, 1+SUM(IncCount)OVER(PARTITION BY cust_id ORDER BY [day]) [rank] --1+SUM(IncCount)OVER(PARTITION BY cust_id ORDER BY [day] ROWS BETWEEN unbounded preceding AND current row) [rank] FROM ( SELECT *, IIF(LAG(price)OVER(PARTITION BY cust_id ORDER BY [day])<>price,1,0) IncCount --CASE WHEN LAG(price)OVER(PARTITION BY cust_id ORDER BY [day])<>price THEN 1 ELSE 0 END IncCount FROM Test ) q 

The second option without LAG

 WITH numCTE AS( SELECT *,ROW_NUMBER()OVER(PARTITION BY cust_id ORDER BY [day]) RowNum FROM Test ) SELECT t1.*, 1+SUM(CASE WHEN t2.price<>t1.price THEN 1 ELSE 0 END)OVER(PARTITION BY t1.cust_id ORDER BY t1.[day]) [rank] --1+SUM(CASE WHEN t2.price<>t1.price THEN 1 ELSE 0 END)OVER(PARTITION BY t1.cust_id ORDER BY t1.[day] ROWS BETWEEN unbounded preceding AND current row) [rank] FROM numCTE t1 LEFT JOIN numCTE t2 ON t2.RowNum+1=t1.RowNum AND t2.cust_id=t1.cust_id 

Third option with recursive CTE

 WITH numCTE AS( SELECT *,ROW_NUMBER()OVER(PARTITION BY cust_id ORDER BY [day]) RowNum FROM Test ), rankCTE AS( SELECT RowNum,cust_id,prod_id,[day],price,1 [rank] FROM numCTE WHERE RowNum=1 UNION ALL SELECT n.RowNum,n.cust_id,n.prod_id,n.[day],n.price, r.[rank]+CASE WHEN n.price<>r.price THEN 1 ELSE 0 END [rank] FROM numCTE n JOIN rankCTE r ON n.RowNum=r.RowNum+1 AND n.cust_id=r.cust_id ) SELECT * FROM rankCTE OPTION(MAXRECURSION 0)