How to get a unique highscore per user

Question

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

Answer 1

Pull the time from the highscores table separately
MySQL doesn't respect ANSI standard aggregations by default: hence your dodgy data

SELECT 
    u.UID, u.ALIAS, u.EMAIL, h.SCORE, h.value1 AS TIME
FROM 
   (
    SELECT user_uid, MAX(score) as SCORE
    FROM highscores
    GROUP BY user_uid
   ) x
   JOIN
   highscores h ON x.user_uid = h.user_uid AND x.SCORE = h.SCORE
   JOIN
   users u ON h.user_uid = u.uid
ORDER BY h.SCORE DESC
LIMIT 0, 10

This assumes that each user does not have multiple high scores with the same value.
If you do, you need a 2nd derived table with an aggregate to nest the x/h JOIN