Ranking scores - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Ranking scores

Author

Topic

mem
Starting Member

28 Posts

Posted - 2004-09-10 : 15:47:36

Hello,

How would I go about creating a ranking column that includes ties. And the people that tie are sorted by name in asc order?

So...

Rank | Name | Scores
====================
1 | Bill | 100
2 | Jon | 98
3 | Sue | 80
3 | Tim | 80
4 | Eric | 75

Thanks.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-10 : 15:52:41

[code]
Declare @myTable table (name varchar(100), score int)
Insert Into @myTable
Select 'Bill', 100
Union All Select 'Jon', 98
Union All Select 'Sue', 80
Union All Select 'Tim', 80
Union All Select 'Eric', 75

Select
rank = (select count(distinct score) From @myTable where score >= A.score),
*
From @myTable A
Order By name
[/code]

Corey

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-09-10 : 15:56:08

Something like:

SELECT (SELECT Count(DISTINCT Scores)
      FROM myTable AS b
      WHERE  a.Scores <= b.Scores) AS Rank
,  a.Name
,  a.Scores
   FROM myTable AS a
   ORDER BY a.Scores DESC, a.Name

-PatP

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-10 : 17:57:12

don't forget the part about handling ties:


Select 
	rank = (select count(distinct score)  
                From @myTable           
                 where score > A.score OR (score = A.score and Name < A.Name)),
	* 
From @myTable A 
Order By name

- Jeff

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-09-10 : 18:03:58

Jeff, This does not appear to be ranking correctly??

Coreys solution is correct and handles ties. Only a slight tweak on the order by clause - per the request:

Order By rank,name

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-10 : 18:24:51

EDIT -- never mind, my bad -- i didn't read carefully enough at the example. he said he wanted ties to be sorted a certain way, i misread that as being ranked a certain way.

if it were to be as I interpreted it, my solution would work but you'd need to remove the DISTINCT which i didn't notice when i posted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources