Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
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

1 Answer 1

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

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

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

 
discard

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.