Group by column and select all fields in rows with multiple minimum values

I need to select all the fields in a row that are grouped by a certain field and contain the minimum of another field (points), using another field to break ties (id).

Example of data:

Desired Result:

So I’d like to return the row of the minimum amount of points scored in each game and the id that scored it. Notice that if there is a tie, like in game y where id 3 and 2 both scored 2 points, i’d like the row with id 2. In the actual table there are more fields with relevant data.

Current Solution:

SELECT g1.*
FROM games g1
  INNER JOIN
  (
    SELECT game, MIN(Point) MinPoint
    FROM games g2
    GROUP BY game
  ) g2
  ON g1.game = g2.game AND g1.point = g2.MinPoint

However, I get duplicate rows where two ids scored the same minimum on a game.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Rank your rows with ROW_NUMBER:

select id, game, points
from
(
  select
    g.*,
    row_number() over (partition by game
                       order by points, id) as rn
  from games g
) ranked
where rn = 1
order by game;

This requires MySQL 8.

Another solution: select all rows for which not exists a better row.

select *
from games g
where not exist
(
  select null
  from games g2
  where g2.game = g.game
  and
  (
    g2.points < g.points
    or
    (g2.points = g.points and g2.id < g.id)
  )
)
order by game;

Method 2

You have also to group at the top query because a join connects all datasets.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0