Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12
  1. #1
    Senior Member nsr81's Avatar
    Join Date
    Oct 2002
    Posts
    1,132
    Member #
    250
    Liked
    15 times
    I'm working on my newest venture, an online game. However, I'm having trouble calculating ranks:

    table structure:
    Code:
    username | password | ..... | score | ....
    That's all, at the moment I can't change the structure, for a few reasons. 1. Not my code, I just bought the script, 2. No time to search and update all the insane inserts.

    Can anyone help me with coming up with a query or simple algorithm to calculate a user's rank.

    What I want to do is to take Top 20 users (easy ). Put some gap, calculate and show currently logged in user's rank and score, and some more gape and the 2 or 3 users who are below currently logged in user.

    example:
    Code:
    1. User   12344
    2. User   12344
    3. User   12344
    4. User   12344
    5. User   12344
       .
       .
    20. User   12344
    
         << Spacer >>
    
    99. YOU  099
    
       << Spacer >>
    
    100. Someone 073
    101. bob      065
    I could use the following queries:
    Code:
    SET @rank=0;
    SELECT @rank:=@rank+1 AS rank, username, score FROM users ORDER  BY score DESC
    But this forces me to select all users in order to get the rank of the current user.

    $5 TD for anyone with insight, that might lead to a possible solution.

    Sorry about being long winded, and thanks for reading this far.

    - nsr81
    There and Back Again :Ogre:

  2.  

  3. #2
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    something like
    show top 20
    show score of current user (select by user name)

    Now, order it by the score total. Then increment table twice. At least I think this will work. Does each user have a unique ID number?
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  4. #3
    Senior Member nsr81's Avatar
    Join Date
    Oct 2002
    Posts
    1,132
    Member #
    250
    Liked
    15 times
    Showing the entries is not a problem. I want to caculating their ranking, in integers, i.e. top score is rank # 1, etc.
    There and Back Again :Ogre:

  5. #4
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Do you want these ranks to be stored in the table, or just calculated on the fly when someone asks for them?
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  6. #5
    Senior Member nsr81's Avatar
    Join Date
    Oct 2002
    Posts
    1,132
    Member #
    250
    Liked
    15 times
    On the fly would be best.
    There and Back Again :Ogre:

  7. #6
    Senior Member Brak's Avatar
    Join Date
    Apr 2003
    Location
    San Francisco, CA
    Posts
    3,413
    Member #
    1217
    Liked
    2 times
    I've actually built an online game int he past

    The idea is to only update ranks every hour or so becaue it's a BITCH on the server. There's no way around sorting the entire list as it has to check everyone's score no matter what you do.

    But... you can select it in ways that are more efficient than others.. and I've forgotten all of these ways lol
    Kyle Neath: Rockstar extraordinare
    The blog | The poetry site | The Spore site

  8. #7
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Let's see.... try something like this:
    Code:
    SELECT
        (SELECT COUNT(*) + 1 FROM users WHERE score > u.score) rank,
        username,
        score 
    FROM 
        users u
    ORDER  BY score DESC
    LIMIT 5
    This query will also show ties ranked like this - 1, 2, 2, 4, 5 - sharing the tied spot, which is preferred.

  9. #8
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    incidentally, the query above uses a COLLATED SUB-QUERY, which is only supported by some versions of MySQL (not sure which), and is also VERY SLOW for queries of an entire table. Limiting the results makes it efficient, but if you're gonna do this with an entire table, you're dead meat

  10. #9
    Senior Member nsr81's Avatar
    Join Date
    Oct 2002
    Posts
    1,132
    Member #
    250
    Liked
    15 times
    4.1 only
    There and Back Again :Ogre:

  11. #10
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    A database without sub-query support is crippled.

    You should just order by the score DESC, limit 5, and get the count in your code with a counter.


Page 1 of 2 1 2 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Search tags for this page

mysql calculate rank

,

mysql for calculating rank

,

ranking scores in mysql

Click on a term to search for related topics.
All times are GMT -6. The time now is 11:55 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com