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

2 Responses to SQL: Top (Latest) Item Per Group

  1. e cigaret says:

    e cigaret…

    […]SQL: Top (Latest) Item Per Group « All Wrong[…]…

  2. parallels desktop 10 for mac activation key

    SQL: Top (Latest) Item Per Group | All Wrong

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: