T-SQL Ranking Problem

I'm trying to code a way to determine a person's rank from my REGISTRATION table. Here's a brief sample of what the table looks like:

REGISTRATION

student    event   conference_id   score
---------    -------   -----------------   -------
Bob          2          1                        16
Jim           2          1                         12
Kim          2          1                          21
Bob         3          1                          70
Juan        3          1                         55
Jack        2          2                         18
Sally        2         2                         24

What I want to do is to be able to pull all of Bob's events and display his score and rank for each event. But here is where I'm having trouble ... his rank can only be determined within his conference.

Thanks,

Paul
paulnetAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
select * from (
select ranking = dense_rank() over(partition by event, conference order by score desc), *
from events
) a
order by event, conference, ranking desc
0
 
chapmandewCommented:
using sql 2005?
0
 
paulnetAuthor Commented:
Wow, chapmandew, that works. I'm going to do a few more tests with my app before I accept the solution.

BTW, yes, it is SQL 2005

Thank you so much!!
0
 
chapmandewCommented:
very welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.