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:
1 2 3 4 5 6 7 8 9 10 |
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.