return max value for each group but when there is a tie, return one with lower id in MySQL

Question

I am using MySQL 8.0

My table looks like this:

group   user_id    score
  A        1        33
  B        2        22
  A        3        22
  B        4        22

I want it to return

group   user_id score
  A        1     33
  B        2     22

note that even though group B has same score user_id=2 is final winner since he/she has lower user_id

How to improve from below query...?

SELECT group, user_id, max(score)
  from table

Thanks in advance!

Answer 1

@Ambleu you are on the right track using MAX(), but to do this you need to use it in addition to MIN(), and also use a sub query to get the MAX(score) like this:

SELECT `mt`.`group`,
    MIN(`mt`.`user_id`) AS `user_id`,
    `mt`.`score`
FROM `myTable` AS `mt`
JOIN (SELECT `group`,
        MAX(`score`) AS `score`
      FROM `myTable`
      GROUP BY `group`) AS `der` ON `der`.`group` = `mt`.`group`
        AND `der`.`score` = `mt`.`score`
GROUP BY `mt`.`group`, `mt`.`score`

Here are your tables and the solution query mocked up on db-fiddle.

If this doesn't get you what you need please let me know and I'll try to assist further.