SQL: Top (Latest) Item Per Group

I spent far too long this morning on trying to find a solution to this tricky SQL problem. Eventually, I found the answer by Googling. I’ve copied the solution here in case I need it again.

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

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.

Advertisements