SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Rank Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gavakie
Posting Yak Master

221 Posts

Posted - 10/08/2012 :  11:38:52  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/08/2012 :  12:35:23  Show Profile  Reply with Quote
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
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 10/08/2012 :  13:03:31  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
How do I make it so the hightest rank is the lowest number?
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 10/08/2012 :  13:38:26  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
Right now when i run it that way it has the highest ranked person as 73 and everyeone else 2
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/08/2012 :  14:19:30  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000