Ranking Students by Grade in SQL

sql, sql-server, t-sql

Solution

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?

Problem

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?

Original source