CODEWITHSUNDEEP
mysqlselect
chinh
chinh

Reputation: 291

mysql select order issue

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

Answers (3)

Prabhakar Manthena
Prabhakar Manthena

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

Babar Al-Amin
Babar Al-Amin

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

Fabio
Fabio

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

Related Questions