Reputation: 57
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.
Upvotes: 0
Views: 135
Reputation: 838186
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
Upvotes: 2
Reputation: 332571
there is a problem. When i calculate the difference of days (today-this of dateachieved) the result is wrong.
There's two issues
dateachieved
isn't likely to be the value associated with the high scoredateachieved
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.
Upvotes: 3
Reputation: 9361
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?
Upvotes: 0
Reputation: 124768
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);
Upvotes: 1