Options for SELECTing a column not in GROUP BY clause?

I am a beginner in SQL. I have the following ‘animal’ table:

RACE  NAME   AGE
dog   Medor  10
dog   Arthur 17
dog   Pitou  9
cat   Kitty  11
cat   Milo   5
cat   Loki   7
dog   Jack   14

I want to write a query to get the maximum age of each race, as well as the name of the associated animal. I tried:

SELECT race, name, MAX(age) FROM animal GROUP BY race

It does perform the per-race grouping, and does return the maximum age of each race, however, the name of the animal in each race seems random. Basically, it returns

RACE  NAME   MAX(age)
dog   Medor  17
cat   Kitty  11

whereas I would like it to return

RACE  NAME   MAX(age)
dog   Arthur 17
cat   Kitty  11

I read about ONLY_FULL_GROUP_BY and understood SQL will select any value for the NAME column, whereas I expected it to select the value associated with the maximum age.

The only other way I can imagine would be to do a sub-query, but this seems very complicated.

SELECT race, name, age FROM animal B WHERE age = (SELECT MAX(age) FROM animal A WHERE A.race = B.race)

I am surprised there is not a simpler solution. I have two questions:

• is it correct my original query should not work (ie. produce the expected result) in the first place?
• is there a better way than doing a subquery?

I’m using MySQL 8.x.

>Solution :

The typical solution is to use ROW_NUMBER() to identify the rows you want.

For example:

select *
from (
  select a.*,
    row_number() over(partition by race order by age desc) as rn
  from animal a
) x
where rn = 1