mysql - Get records with max value for each group of grouped SQL results

Question

I´m refering to this post from Yarin in 2012.

this won't work anymore, im using mysql v 8.0.19

Given a table like that below, with person, group, and age columns, how would you get the oldest person in each group? (A tie within a group should give the first alphabetical result)

Person | Group | Age
---
Bob  | 1     | 32  
Jill | 1     | 34  
Shawn| 1     | 42  
Jake | 2     | 29  
Paul | 2     | 36  
Laura| 2     | 39

Desired result set:

Shawn | 1     | 42    
Laura | 2     | 39  

Answer 1

You can use row_number():

select *
from (
    select t.*, row_number() over(partition by group order by age desc, person) rn
    from mytable t
) t
where rn = 1

The accepted answer to the linked post baffles me somehow. It relies on option ONLY_FULL_GROUP_BY being disabled (well, ok, that was the default in MySQL 5.6). It makes assumptions about the way MySQL behaves in that case, which I don't think are officially documented anywhere. I wouldn't recmmend that, even in MySQL 5.6; happily, there is another answer, with more upvotes, that seem like a much safer approach to the question.