T-SQL Ranking Problem

Posted on 2009-12-28
Medium Priority
Last Modified: 2013-12-24
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:


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.


Question by:paulnet
  • 3
LVL 60

Expert Comment

ID: 26135350
using sql 2005?
LVL 60

Accepted Solution

chapmandew earned 2000 total points
ID: 26135355
select * from (
select ranking = dense_rank() over(partition by event, conference order by score desc), *
from events
) a
order by event, conference, ranking desc

Author Comment

ID: 26135507
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!!
LVL 60

Expert Comment

ID: 26135524
very welcome!

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question