Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

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
LIMIT 0, 10
share|improve this question
Which RDBMS???? – Thomas Stringer Mar 26 '12 at 12:19
RDBMS: I use MySQL. – Mattias Mar 26 '12 at 12:26
up vote 1 down vote accepted

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

    u.UID, u.ALIAS, u.EMAIL, h.SCORE, h.value1 AS TIME
    SELECT user_uid, MAX(score) as SCORE
    FROM highscores
    GROUP BY user_uid
   ) x
   highscores h ON x.user_uid = h.user_uid AND x.SCORE = h.SCORE
   users u ON h.user_uid = u.uid
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

share|improve this answer
Thanks! And this way I am sure that TIME is collected from the same "post" as SCORE? What if there are more values? Like value2, value3? And what happens if the user has two highscores with the same value? – Mattias Mar 26 '12 at 16:04

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.