views:

407

answers:

4

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?

+28  A: 

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?

Eric
+1 nice answer, you learn something new every day :)
Robert Greiner
+1 - yep, never heard of `rank`
John Rasch
The `over` clause is amazing. You can do any aggregate function with a `partition by`. Very neat stuff.
Eric
Wow... that was impressing... but it said that Rank() takes exactly 0 arguments, so I just rid of the content within parenthesis and it worked perfect... Thank you
Ike
+1 Excellent, now doing: INSERT INTO _Brain (Info) Values (@Nugget) :)
Darknight
+1  A: 

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.

dguaraglia
A: 

Do you know about using an ORDER BY clause?

JB King
A: 

You need to write a function that will computer the rank for a given student and date. Then you can "ORDER BY Date, Rank()"

Jason
This is an incredibly bad idea. The rank() function would need to consult the table, and I know that sql server will not merge it into the query (not sure about other DBMSs though, but it would surprise me).
erikkallen