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

## All we need is an easy explanation of the problem, so here it is.

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

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂