PHP MySQL Highscore table

Question

Im joining 3 tables to present a table with users highest score

My tables

game_log:

 ---ID---user_ID---score---time---
 |   1      52      567     10   |
 |   2      53      641     13   |
 |   3      52      465     8    |
 |   4      53      451     14   |
 --------------------------------- 

users:

 ---ID---name---countyid---
 |   52  Dave      1      |
 |   53  John      2      |
  ------------------------

county:

  ---countyid---countyname---
  |      1    Orange wichit |
  |      2    Orange clemts |
   --------------------------

SQL:

SELECT * FROM game_log 
INNER JOIN users ON game_log.user_ID=users.ID 
INNER JOIN county ON users.countyid=county.countyid 
ORDER BY game_log.score DESC , game_log.time LIMIT 20";

Above code gives me this result:

Rank---Name--------County------Score---Time
1      John     Orange clemts   641     13
2      Dave     Orange wichit   567     10
3      John     Orange clemts   465     8
4      Dave     Orange wichit   451     14

My problem is that I want the highscore table to display the top 20 users with the highest score, not the 20 highest scores.

Like this:

Rank---Name--------County------Score---Time
1      John     Orange clemts   641     13
2      Dave     Orange wichit   567     10

Need som help with this, not familiar with joining tables ;-)

Answer 1

This approach will show the top 20 users and each user's highest score, and if they have multiple instances of the same score, it'll show the information for the earliest one (lowest time value for that user and score).

SELECT *
  FROM game_log gl
 INNER JOIN users u
    ON gl.user_ID = u.ID
 INNER JOIN county c
    ON u.countyid = c.countyid
 WHERE not exists (select 1
          from game_log gl2
         where gl2.user_id = gl.user_id
           and gl2.score > gl.score)
   and not exists (select 1
          from game_log gl2
         where gl2.user_id = gl.user_id
           and gl2.time < gl.time
           and gl2.score = gl.score)
 ORDER BY gl.score DESC, gl.time LIMIT 20;

Without doing this, if the same user in the top 20 had the same score 2+ times, they would be listed 2+ times, and you would not get back 20 people by using LIMIT 20 because the same person would be taking up N rows out of that 20.

SQL Fiddle here showing data with a tie: http://sqlfiddle.com/#!2/0ac931/5/0