Rank Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rank Help

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2012-10-08 : 11:38:52

I have a query where I am ranking the data. one of my rows may have quite a few ties. I want the ties to be ranked by the highest number. I.E.

if i have 10 people
and 7 are tied I dont want there rank like tihs.

1
2
3
3
3
3
3
3
3
3
I would rather have it ranked
1
2
7
7
7
7
7
7
7
7
Any ideas?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-08 : 12:35:23

How did you calculate rank=7 for the threes's? Given that you have eight three's, I can see the rank being 3, or the rank being 10. Can you explain the logic used to get 7?

If you want to assign rank=3 to the three's (which is what usually people do):

SELECT *,
   RANK() OVER(ORDER BY col1) as [Rank]
FROM YourTable;

If you want to assign rank as 10 to the three's:

SELECT *,
   RANK() OVER(ORDER BY col1) + COUNT(*) OVER(PARTITION BY col1) -1
FROM YourTable

gavakie
Posting Yak Master

221 Posts

Posted - 2012-10-08 : 13:03:31

How do I make it so the hightest rank is the lowest number?

gavakie
Posting Yak Master

221 Posts

Posted - 2012-10-08 : 13:38:26

Right now when i run it that way it has the highest ranked person as 73 and everyeone else 2

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-08 : 14:19:30

quote:
Originally posted by gavakie

Right now when i run it that way it has the highest ranked person as 73 and everyeone else 2

That seems strange, but I don't know why, since I don't have the data that you are using. Code below constructs some sample data and ranks them different ways. Take a look and see if any of that will work for you.

CREATE TABLE #tmp (col1 INT);
INSERT INTO #tmp VALUES (10),(11),(31),(31),(31),(48),(48),(100);

SELECT *,
   RANK() OVER(ORDER BY col1) as [Rank]
FROM #tmp ORDER BY [Rank];

SELECT *,
   RANK() OVER(ORDER BY col1) + COUNT(*) OVER(PARTITION BY col1) -1 as [Rank]
FROM #tmp ORDER BY [Rank];

SELECT *,
   RANK() OVER(ORDER BY col1 DESC) as [Rank]
FROM #tmp ORDER BY [Rank];

SELECT *,
   RANK() OVER(ORDER BY col1 DESC) + COUNT(*) OVER(PARTITION BY col1) -1 as [Rank]
FROM #tmp ORDER BY [Rank];

DROP TABLE #tmp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources