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.
e cigaret…
[…]SQL: Top (Latest) Item Per Group « All Wrong[…]…
parallels desktop 10 for mac activation key
SQL: Top (Latest) Item Per Group | All Wrong