0

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

1

1 Answer 1

0

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

3
  • This solution worked, It takes 13 min to run on a table with 5Million records, Thanks Mar 23, 2021 at 17:43
  • @ChristopherMartinez If you paste your table/index estructure, maybe I or someone else finds a faster solution.
    – McNets
    Mar 23, 2021 at 17:59
  • Post a SHOW CREATE TABLE my_table\G for your table. Also, please provide the cardinality of your different fields! With suitable indexes, this performance can be greatly improved! I'd suggest, (grp, age DESC)!
    – Vérace
    Mar 24, 2021 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.