Ranking Students by Grade in SQL

Question

I have a table like this:

Date       StudentName    Score

01.01.09   Alex           100
01.01.09   Tom            90
01.01.09   Sam            70
01.02.09   Alex           100
01.02.09   Tom            50
01.02.09   Sam            100

I need to rank the students in the result table by score within different dates, like this:

Date       Student         Rank

01.01.09   Alex             1
01.01.09   Tom              2
01.01.09   Sam              3
01.02.09   Alex             1
01.02.09   Sam              1
01.02.09   Tom              2

How can I do this in SQL?

Answer 1

You want to use the rank function in T-SQL:

select
    date,
    student,
    rank() over (partition by date order by score desc) as rank
from
    grades
order by
    date, rank, student

The magic is in the over clause. See, it splits up those rankings by date, and then orders those subsets by score. Brilliant, eh?

Answer 2

You should use ORDER BY:

SELECT * FROM Students ORDER BY Date,Rank

That will order the data by date, then rank. You can add as many fields as you want, as long as they are comparable (you can't compare BLOBs or long text fields).

Hope that helps.