Ranking item by col value in SQL

Question

Given the following table contents:

StudentNum    Score
 1             100%
 2             95%
 3             99%
 4             0%
 5             12%

How would I get the ranking of "#2 out of 5" for StudentNum=3.

The only way I can think of doing it is in the application code, getting all the items and then getting the index. For example:

items = SELECT StudentNum FROM table ORDER BY score DESC
student_3_position = items.index('3') + 1
total_positions = len(items)
'Student3 is #%s out of %s' % (student_3_position, total_positions)

Is there a way to do this directly in SQL?

See: http://stackoverflow.com/questions/3333665/rank-function-in-mysql. — mfjones, Feb 25 '15 at 04:31

score 0 · Accepted Answer · answered Feb 25 '15 at 07:37

Preparing data:

 create table dbo.temp (StudentNum int identity (1,1), Score int)
 insert into dbo.temp values (100)
 insert into dbo.temp values (95)  
 insert into dbo.temp values (99)
 insert into dbo.temp values (12)
 insert into dbo.temp values (0)

Result:

select *, ROW_NUMBER() OVER (ORDER BY Score desc) as 'Rank' from dbo.temp order by StudentNum

Ranking item by col value in SQL

1 Answers1