sql – YAGNI

I spent far too long this morning on trying to find a solution to this SQL problem.
Consider the PERSON table:

Location	Name	Age
London	Fred	45
London	Mark	35
London	Mike	25
Cardiff	Jim	56
Cardiff	Julia	46
Cardiff	Murray	36
Edinburgh	Sheila	61
Edinburgh	Launa	51
Edinburgh	Lin	41

Suppose you want the name of the oldest person at each location. How do you achieve this in SQL?

Like this:

SELECT [name],
       location,
       age
  FROM person p
 WHERE age = (
    SELECT MAX(age)
      FROM person
     WHERE p.location = location
  )
  ORDER BY location

SELECT [name],

location,

age

FROM person p

WHERE age = (

SELECT MAX(age)

FROM person

WHERE p.location = location

)

ORDER BY location

The result should be:

Location	Name	Age
London	Fred	45
Cardiff	Jim	56
Edinburgh	Sheila	61

Thanks to Findy Services and B. Jacobs for this solution.

Tag: sql

SQL: Top (Latest) Item Per Group