CODEWITHSUNDEEP
sql-server-2012
Shaul Behr
Shaul Behr

Reputation: 38003

SQL query to rank items

I have a data set in which I need to rank rows by a score column. Obvious answer is to use ROW_NUMBER():

select ID, Name, Score, ROW_NUMBER() over (order by Score desc) as Rank
from MyTable
order by Score desc

but there's a catch. If two records have the same score, then they must have the same rank, and the record that comes after them must skip back to reflect their natural order, e.g.

ID      Name        Score       Rank
---     ----        -----       ----    
13      Fred        47          1   
77      Bob         36          2   
88      Harry       36          2
10      John        23          4

What's the best way to do this?

Upvotes: 0

Views: 29

Answers (1)

Dhaval
Dhaval

Reputation: 2379

Try This

select ID, Name, Score, RANK() over (PARTITION BY Score ORDER BY Score DESC) as Rank from MyTable

Upvotes: 1

Related Questions