CODEWITHSUNDEEP
mysql
Tin Tran
Tin Tran

Reputation: 6202

How to get a rows data that has the highest score for each player

For example I have the below data.

PlayerId, data, score
1, 0, 20
1, 1, 10
2, 0, 30
2, 1, 40
2, 0, 40

How do i get the below result?

PlayerId, data, score
1, 0 20
2, 1, 40

for row 2 of result it doesn't matter if it returns data 0 instead of 1 because 40 is max score for 0 and 1, returning either one is okay I just one row returned for each player with a possible data of he max(score).

Upvotes: 0

Views: 40

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

SELECT PlayerId,MAX(`data`) AS `data`, MAX(score) AS score
FROM your_table
GROUP BY PlayerId

EDIT:

The first version will get the both MAX values. Now I see you want data from actual row:

SELECT PlayerId, score, MAX(`data`) AS `data`
FROM your_table
WHERE (PlayerId, score) IN (SELECT PlayerId, MAX(score)
                          FROM your_table
                          GROUP BY PlayerId)
GROUP BY PlayerId, score;

SqlFiddleDemo

Upvotes: 5

Related Questions