November 13, 2005 at 10:22 am
Hi all,
I have following score table (I also have Game and User table):
id (pk)
gameid (int)
userid (int)
score (int)
comment (text)
I want to have have following result (where gameid=1 eg):
userid highscore comment
20 90000 great!
21 80000 ok
19 70000 Good
...
How do I achieve this at best when each user can have different scores per game (I only need highest score per game per user)
I also need the comment associated with the highest score for that user
November 13, 2005 at 5:10 pm
For the case when the same user has more than one row that has the same high score that is the greatest, then the comment to be return will be the highest sorted, try this
select UserHighScore.userid
, UserHighScore.highscore
, UserHighScoreComments.comment
FROM ( select gameid , userid, max(highscore) as HighScore
from Scores
group by gameid , UserId
) as UserHighScore
join (select gameid , userid, highscore, MAX(comments) as comments
from Scores
group by gameid , UserId, highscore)
as UserHighScoreComments
on UserHighScoreComments.gameid = UserHighScore.gameid
and UserHighScoreComments.userid = UserHighScore.userid
and UserHighScoreComments.HighScore = UserHighScore.HighScore
where UserHighScore.gameid = 1
SQL = Scarcely Qualifies as a Language
November 14, 2005 at 1:38 am
ok, thanks for your reply! Gonna try this soon.
Let you know if it worked.
Dario
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply