1

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
  • Which RDBMS???? – Thomas Stringer Mar 26 '12 at 12:19
  • RDBMS: I use MySQL. – Mattias Mar 26 '12 at 12:26
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

  • 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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