November 10, 2011 at 3:23 am
Hey all,
Its a whille since i used rank last.
I cant seem to get it to do what i want. Example code below:
CREATE TABLE #DATA (TOTAL INT)
INSERT INTO #DATA
VALUES (1),(3),(3),(5),(5),(5)
SELECT RANK() OVER
(PARTITION BY TOTAL ORDER BY TOTAL DESC) AS 'RANK',
* FROM #DATA
What i want to come back is this:
RANKTOTAL
11
23
23
35
35
35
However what is coming back is this:
RANKTOTAL
11
13
13
15
15
15
Can anyone help me (or point me in the correct direction)
TIA
Dan
November 10, 2011 at 3:41 am
Doh Sorry i worked it out. I didnt need the partition.
November 10, 2011 at 3:46 am
From your expected results, DENSE_RANK() seems to be what you want to use (the same as RANK, but the rank number doesn't increment after ties):
SELECT DENSE_RANK() OVER
(ORDER BY TOTAL ) AS 'RANK',
* FROM #DATA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply