Results 1 to 4 of 4

Thread: Creating a highscore list(only taking one with highest score in case of same usrname)

  1. #1
    Join Date
    Aug 2006
    Posts
    58
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Arrow Creating a highscore list(only taking one with highest score in case of same usrname)

    Hi guys,

    I have a question about retrieving data from MySQL to create a highscore list, I hope you wouldn't mind to help me

    Here is what my MySQL table looks like:

    Code:
    +-----------+----------------------------+-----------------------------+------------+
    | personID  | username                   | time_used                   | score      |
    +-----------+----------------------------+-----------------------------+------------+
    |         1 | john                       | 10                          |        30  |
    |         2 | george                     | 20                          |        29  |
    |         3 | john                       | 50                          |        29  |
    |         4 | bill                       | 25                          |        32  |
    |         5 | bill                       | 30                          |        40  |
    |         6 | josh                       | 40                          |        50  |
    |         7 | bill                       | 50                          |        26  |
    |         8 | george                     | 60                          |        48  |
    +-----------+----------------------------+-----------------------------+------------+
    I created a quiz and for simplicity I just let the user decide their username each time they take the test. So there will be multiple username like that in the table.

    Now I have figured out how to sort it out based on score (first priority) and time_used (second priority). The problem is that the double username will all be included in the highscore list.


    Is there anything I could do in the MySQL SELECT process (or other alternative perhaps?) that would let me exclude the duplicate usernames? So only the upper-most username (after sorted out using the priorities above) out of the multiple usernames will be included.

    Thank you in advance, I would greatly appreciate it if you could give me some pointers about this

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,718
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    Code:
    SELECT personID, username, time_used, MAX(score) FROM tablename GROUP BY username
    This finds and displays only the max score for each username. Hence username was used for the GROUP BY. You can read more about GROUP BY here.
    Last edited by james438; 03-20-2012 at 02:26 PM. Reason: grammar
    To choose the lesser of two evils is still to choose evil. My personal site

  3. The Following User Says Thank You to james438 For This Useful Post:

    k12onos (04-01-2012)

  4. #3
    Join Date
    Aug 2006
    Posts
    58
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Default

    Hi, thanks for the reply

    It worked if used by itself but it doesn't work if used using ORDER BY

    Code:
    SELECT personID, username, time_used, MAX(score) FROM tablename GROUP BY username ORDER BY score DESC, time_used, personID
    I used this but it showed up strangely. The output does not show the "score" values, it is sorted out correctly though..

    Am I doing something wrong here?

    edit: nevermind, I forgot to use MAX(score) when calling out the score values :P

    Thanks a lot!!
    Last edited by k12onos; 03-24-2012 at 05:04 AM.

  5. #4
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,718
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    Glad you figured it out.

    I can't see what the rest of your query listed after DESC does. I would remove it to avoid potential errors. If it does do anything I am curious to know what it does.
    To choose the lesser of two evils is still to choose evil. My personal site

  6. The Following User Says Thank You to james438 For This Useful Post:

    k12onos (04-01-2012)

Bookmarks

Posting Permissions

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