PHP MySQL Highscore table

join, mysql, php, sql

Solution

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

Problem

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 ;-)

Original source