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:
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.
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.
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.
Rank your rows with
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;
You have also to group at the top query because a join connects all datasets.