Reputation: 291
I have a table like that
id UserID score create_time
7 002YEXEKF9 120000 2013-06-06 14:04:52
8 0008FV2FN5 120 2013-06-06 15:10:39
9 0008FV2FN5 130 2013-06-06 16:57:27
10 DT5LP0V8AH 1200 2013-06-06 15:13:44
11 DT5LP0V8AH 1400 2013-06-06 16:31:03
I use this query for select data:
SELECT userid, score FROM high_score ORDER BY score DESC
The result:
userid score
002YEXEKF9 120000
DT5LP0V8AH 1400
DT5LP0V8AH 1200
0008FV2FN5 130
0008FV2FN5 120
As you can see that user_id DT5LP0V8AH,0008FV2FN5 has two score.I don't want this.I want show the highest score of users. All I want the result like this:
userid score
002YEXEKF9 120000
DT5LP0V8AH 1400
0008FV2FN5 130
Can anyone fix the sql?.I really appreciate.
Upvotes: 1
Views: 50
Reputation: 2313
You have to use MAX
, GROUP BY
to get the result you wanted to.
SELECT userid, MAX(score)
FROM high_score
GROUP BY userid ORDER BY score DESC
Find more info on MAX.
Find more info on GROUP BY
I hope it will work.
Upvotes: 0
Reputation: 3984
This should work (Haven't tested).
SELECT userid, MAX(score) max_score FROM high_score GROUP BY userid ORDER BY max_score DESC
Upvotes: 0
Reputation: 23510
I think you can use MAX()
to get the highest score and GROUP BY
aggregate function to group by userid
SELECT userid, MAX(score)
FROM high_score
GROUP BY userid
ORDER BY score DESC
Upvotes: 2