Post reply

Build a leaderboard

  • May 25, 2005 at 6:48 am

    #93062

    Hi, I was just wondering about how to go about accomplishing something.  I've got a database where I keep track of match points for players during a golf league season.  How would I construct a query that would return a result set like:

    Position      Player       Points

    1               Joe          40

    2               John        38

                    Joan         38

    4              Jimmy        36

                    Jezabel      36

                    Jack          36

    7              Jaime         34

     

    I've got a table that gets rows added to it after processing individual scores for each week  That table has the player and points for each week they play.  Any suggestions?

    Thanks!

  • May 25, 2005 at 7:06 am

    #560936

    can you post the table definition along with some sample data?

  • May 25, 2005 at 7:17 am

    #560943

    To be more specific, I'd like to figure out a way to do the ranking portion.  I can build everything else but was wondering if someone had done something like the rank.  The person with the most points is #1.  The person with the next most points would be #2.  If anyone is tied with that person they would be ranked at #2 as well.

    The only way I can think of would be to build a temp table with all the unique sums.  Then run through each one with another query and a counter.

    I'm not looking for code here, just avenues to explore

    Thanks!

  • May 25, 2005 at 7:23 am

    #560949

    Well this is really something that should be done on the client side of the app... but if you insist on doing this on the server :

    Select C1.Name, (Select count(*) from dbo.SysColumns C2 where C2.Name < C1.Name) + 1 as Rank from dbo.SysColumns C1 order by C1.name

    Please note that this query takes 11 seconds to run for only 3.5 k rows.

  • May 25, 2005 at 7:23 am

    #560948

    SQL Server 2005 has new ranking functions that let you do this. With SQL Server 2000 every solution will be some sort of not-so-pretty solution. In any case I see no specific reason to do this in the database. Just sort the resultset on Points DESC and when presenting the rows in the client (in a table or something) add the rank by keeping count of it in client code.

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • May 25, 2005 at 2:08 pm

    #561096

    Thanks for that bit of code Remi!  I've got a ranking working now for the league teams and the response is very fast.  Since the largest that table will ever be is 800 records (20 teams x 20 weeks x 2 Players/team), I think I can get away with this one!

  • May 25, 2005 at 2:10 pm

    #561098

    k.. Good luck with this project.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply