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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply