SELECT max value for each group of values order by grouped by MYSQL 8.x

Question

I need to get the max age of a group of records, even is the age is duplicate ORDER BY the age DESC

Person | Group | Age
---
Bob  | 1     | 32  
Jill | 1     | 34  
Shawn| 1     | 42  
Jake | 2     | 29  
Paul | 2     | 36  
Laura| 2     | 39  
Lalo |3      |50
Julia|3      |50 
Travis|3     |18



Wanted results: 
Julia | 3     | 50
Shawn | 1     | 42    
Laura | 2     | 39 

I tried this solutions by when applying to my real table i brings duplicated age records and i cant get to Order by 'column' DESC

I also tried by doing GROUP BY Group and ORDER BY Age DESC at the end of the query, this brings the values grouped correctly but the Age values are not right

Answer 1

On MySQL 8.0 one solution is using ROW_NUMBER function in this way:

_{Notice I've used Grp instead of Group, because it's a reserved word.}

SELECT
  Person,
  Grp,
  Age
FROM
  (SELECT
    Person, Grp, Age,
    ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY Age DESC) Rn
  FROM
    Ppl) a
WHERE
  Rn = 1
ORDER BY
  Age DESC;

Person | Grp | Age
:----- | --: | --:
Lalo   |   3 |  50
Shawn  |   1 |  42
Laura  |   2 |  39

There is no way to get Julia instead of Lalo, unless you add another column to the ORDER BY clause.

db<>fiddle here