T-SQL Ranking Problem

Posted on 2009-12-28
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
    LVL 60

    Expert Comment

    using sql 2005?
    LVL 60

    Accepted Solution

    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

    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

    very welcome!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now