November 12, 2010 at 11:07 am
Hi,
I need to update a field (Order_Rank) to display 'rank values' based on other columns within the same table.
The Table has a Date field (DateRequested yyyy-mm-dd) and a Text field (ProductGroup) which are to be grouped on. I require the Order_Rank column to be updated by ProductGroup and by DateRequested DESC.
BEFORE
DateRequested ProductGroup
2010-10-06 DDO
2010-10-06 DDO
2010-10-06 DDC
2010-10-06 DDB
2010-10-06 DDB
2010-10-08 DDO
2010-10-08 DDO
AFTER
DateRequested ProductGroup Order_Rank
2010-10-06 DDO 3
2010-10-06 DDO 3
2010-10-06 DDC 1
2010-10-06 DDB 2
2010-10-06 DDB 2
2010-10-08 DDO 2
2010-10-08 DDO 2
2010-10-08 DDB 1
2010-10-10 DDO 1
Any ideas please?
Thanks in advance,
November 12, 2010 at 11:37 am
Something like this?
;WITH CTE (DR, PG, R) AS
(SELECT DateRequested, ProductGroup, DENSE_RANK() OVER (PARTITION BY ProductGroup ORDER BY DateRequested DESC)
FROM dbo.MyTable)
UPDATE ...
SET MyRankColumn = R
FROM ...
INNER JOIN CTE
ON ....DateRequested = CTE.DR
AND ....ProductGroup = CTE.PG;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 12, 2010 at 11:45 am
Try using DENSE_RANK() to derive your Order_Rank column value, something like this:
...
DENSE_RANK() OVER (
PARTITION BY ProductGroup
ORDER BY DateRequested DESC) AS Order_Rank
However, there may be a small complication if your DateRequested column can potentially contain a time component that you want to ignore in the ranking.
...
DENSE_RANK() OVER (
PARTITION BY ProductGroup
ORDER BY DATEDIFF(day, 0, DateRequested) DESC) AS Order_Rank
EDIT: ...beaten to the line yet again!
You can also update the table through the CTE like this:
;WITH cteRank AS (
SELECT Order_Rank,
DENSE_RANK() OVER (
PARTITION BY ProductGroup
ORDER BY DateRequested DESC) AS Calculated_Rank
FROM MyTable
)
UPDATE cteRank SET Order_Rank = Calculated_Rank
November 12, 2010 at 1:10 pm
Thanks guys.
I tried all variations and ended up using andrew's 3rd option with cte.
Much appreciated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply