CODEWITHSUNDEEP
sqlmysql
Luca Bernardi
Luca Bernardi

Reputation: 4199

Aggregate keeping the row with the max value

Suppose you have the following schema (id, user_id , score). How can I take per each user the row with max score and then order all row for score. In other word I want a ranking where each user have his best result.

Upvotes: 0

Views: 187

Answers (3)

Salil
Salil

Reputation: 47482

select @rownum := @rownum + 1 AS rank, user_id, MAX(score) as Score
      from table_name t,
      (SELECT @rownum := 0) r
      GROUP BY user_id
      ORDER BY Score

Upvotes: 0

Tommi
Tommi

Reputation: 8608

select user_id, max(score)
from user_scores
group by user_id
order by max(score)

Upvotes: 1

Robert
Robert

Reputation: 268

Should be something like: 

 SELECT UNIQUE user_id, score FROM TABLE 
    ORDER BY SCORE DESC

Upvotes: 0

Related Questions