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.

Answer 2

You can do it this way for example

CREATE TABLE table1 (
  `Person` VARCHAR(5),
  `Group` INTEGER,
  `Age` INTEGER
);

INSERT INTO table1
  (`Person`, `Group`, `Age`)
VALUES
  ('Bob', '1', '32'),
  ('Jill', '1', '34'),
  ('Shawn', '1', '42'),
  ('Jake', '2', '29'),
  ('Paul', '2', '36'),
  ('Laura', '2', '39');

SELECT `Person`, `Group`, `Age` FROM table1 s1 WHERE   `Age`=(SELECT MAX(s2.`Age`)
              FROM table1 s2
              WHERE s1.`Group` = s2.`Group`)

Person | Group | Age
:----- | ----: | --:
Shawn  |     1 |  42
Laura  |     2 |  39

db<>fiddle here