MySQL - How to select rows with max value of a field

Question

I have a table of users with their scores for each level of a game:

id | user_id | level | score
1  | David   | 1     | 20
2  | John    | 1     | 40
3  | John    | 2     | 30
4  | Mark    | 1     | 60
5  | David   | 2     | 10
6  | David   | 3     | 80
7  | Mark    | 2     | 20
8  | John    | 3     | 70
9  | David   | 4     | 50
10 | John    | 4     | 30

What is the SQL query needed to get for each level, who has the highest score?

The result should be:

id | user_id | level | score
4  | Mark    | 1     | 60
3  | John    | 2     | 30
6  | David   | 3     | 80
9  | David   | 4     | 50

Thank you

this is a simple group by query. can you post your attempt? — Vamsi Prabhala
– Vamsi Prabhala, Commented Nov 4, 2016 at 1:58

Gordon Linoff · Accepted Answer · 2016-11-04 01:57:52Z

14

If you want to get ties, then you can do something like this:

select s.*
from scores s
where s.score = (select max(s2.score) from scores s2 where s2.level = s.level);

You could get one row per level by aggregating this:

select s.level, s.score, group_concat(s.user_id)
from scores s
where s.score = (select max(s2.score) from scores s2 where s2.level = s.level)
group by s.level, s.score;

This combines the users (if there is more than one) into a single field.

answered Nov 4, 2016 at 1:57

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tuskiomi Over a year ago

This would not work if 2 players with different levels share the max score..

Mak · Accepted Answer · 2016-11-04 02:06:02Z

1

order by score desc in sub query, then select max(score) group by level.

select id, user_id , level , max(score) as score
from
(select * from scores order by score desc)A 
group by level

answered Nov 4, 2016 at 2:06

Mak

1544 bronze badges

Comments

Paul Spiegel · Accepted Answer · 2016-11-04 02:25:26Z

0

If you only want the user, who reached the highest score first (no ties per level):

select *
from users u1
where id = (
    select id
    from users u2
    where u2.level = u1.level
    order by score desc, id asc
    limit 1
)

You should have indexes (id) and (level, score, id)

answered Nov 4, 2016 at 2:25

Paul Spiegel

31.9k5 gold badges47 silver badges56 bronze badges

Collectives™ on Stack Overflow

MySQL - How to select rows with max value of a field

3 Answers 3

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Linked

Related