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

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.

CodePudding user response：

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;

CodePudding user response：

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