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

[k12onos]

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:



+-----------+----------------------------+-----------------------------+------------+
| 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 :)

[james438]

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 (http://www.tizag.com/mysqlTutorial/mysqlgroupby.php).

[k12onos]

Hi, thanks for the reply :)

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


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!!

[james438]

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.