Sql query has unwanted results - Where is the problem?

Question

Hi, I have a table which records users's scores at a game (a user may submit 5,10,20,as many scores as he wants). I need to show the 20 top scores of a game, but per user. (as a user may have submitted eg 4 scores which are the top according to other users's scores) The query i have written is:

SELECT DISTINCT
    `table_highscores`.`userkey`,
    max(`table_highscores`.`score`),
    `table_users`.`username`,
    `table_highscores`.`dateachieved` 
FROM
    `table_highscores`, `table_users` 
WHERE
    `table_highscores`.`userkey` = `table_users`.`userkey`
AND
    `table_highscores`.`gamekey` = $gamekey  
GROUP BY
    `userkey` 
ORDER BY
    max(`table_highscores`.`score`) DESC,
LIMIT 0, 20;

The output result is ok, but there is a problem. When i calculate the difference of days (today-this of dateachieved) the result is wrong. (eg instead of saying "the score was submitted 22 days ago, it says 43 days ago) So,I have to do a second query for each score so to find the true date (meaning +20 queries). Is there any shorter way to find the correct date? Thanks.

Answer 1

You didn't say what language you are using to calculate the difference but I'm guessing it's PHP because of the $gamekey you used there (which should be escaped properly, btw).

If your dateachieved field is in the DATETIME format, you can calculate the difference like this:

$diff = round((time() - strtotime($row['dateachieved'])) / 86400);

Answer 2

I think you need to clarify your question a little better. Can you provide some data and expected outputs and then I should be able to help you further?

Answer 3

In your query you should use an explicit JOIN and you don't need the DISTINCT keyword.

This query should solve your problem. I am assuming here that it is possible for a user to submit the same highscore more than once on different dates, and if that happens then you want the oldest date:

SELECT T1.userkey, T1.score, username, dateachieved FROM (
    (SELECT userkey, max(score) AS score
    FROM table_highscores
    WHERE gamekey = $gamekey
    GROUP BY userkey) AS T1
    JOIN
    (SELECT userkey, score, min(dateachieved) as dateachieved
    FROM table_highscores
    WHERE gamekey = $gamekey
    GROUP BY userkey, score) AS T2
    ON T1.userkey = T2.userkey AND T1.score = T2.score
) JOIN table_users ON T1.userkey = table_users.userkey
LIMIT 20

Answer 4

there is a problem. When i calculate the difference of days (today-this of dateachieved) the result is wrong.

There's two issues

1. the dateachieved isn't likely to be the value associated with the high score
2. you can use MySQL's DATEDIFF to return the the number of days between the current date and the dateachieved value.

Use:

SELECT u.username,
       hs.userkey,
       hs.score,
       DATEDIFF(NOW(), hs.dateachieved) 
  FROM TABLE_HIGHSCORES hs
  JOIN TABLE_USERNAME u ON u.userkey = hs.userkey
  JOIN (SELECT ths.userkey,
               ths.gamekey,
               ths.max_score,
               MAX(ths.date_achieved) 'max_date'
          FROM TABLE_HIGHSCORES ths
          JOIN (SELECT t.userkey,
                       t.gamekey,
                       MAX(t.score) 'max_score'
                  FROM TABLE_HIGHSCORES t
              GROUP BY t.userkey, t.gamekey) ms ON ms.userkey = ths.userkey
                                               AND ms.gamekey = ths.gamekey
                                               AND ms.max_score = ths.score
       ) x ON x.userkey = hs.userkey
          AND x.gamekey = hs.gamekey
          AND x.max_score = hs.score
          AND x.max_date = hs.dateachieved
 WHERE hs.gamekey = $gamekey
 ORDER BY hs.score DESC
 LIMIT 20

I also changed your query to use ANSI-92 JOIN syntax, from ANSI-89 syntax. It's equivalent performance, but it's easier to read, syntax is supported on Oracle/SQL Server/Postgres/etc, and provides consistent LEFT JOIN support.

Another thing - you only need to use backticks when tables and/or column names are MySQL keywords.