Search code examples
mysqlsqlgroup-bymin

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:

id game points hash
1 x 5 ax8
1 z 4 bc4
2 y 2 df8
2 x 1 fd8
3 x 2 fda0
3 y 2 fzlf
3 z 2 z89w

Desired Result:

id game point hash
2 x 1 fd8
2 y 2 df8
3 z 2 z89w

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.


Solution

  • 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;