I have the following tables: users, highscores
I would like to get the top ten best results in highscore. The scores should be unique, meaning one score per user. I tried using
INNER JOIN and
GROUP but this is not returning the correct data. I get the correct uid, score, alias and email but the time is not correct. It seems that it is taken from another highscore post. How do I get the correct data? As soon as we find the "best score" I want everything else (time) from the exact same post.
SELECT UID, ALIAS, EMAIL, SCORE, TIME FROM users t INNER JOIN (SELECT user_uid, MAX(score) as SCORE, value1 as TIME FROM highscores GROUP BY user_uid) x ON x.user_uid = t.uid ORDER BY SCORE DESC LIMIT 0, 10