SQL to get rows (not groups) that match an aggregate
- by xulochavez
Given table USER (name, city, age), what's the best way to get the user details of oldest user per city?
I have seen the following example SQL used in Oracle which I think it works
select name, city, age
from USER, (select city as maxCity, max(age) as maxAge
from USER
group by city)
where city=maxCity and age=maxAge
So in essence: use a nested query to select the grouping key and aggregate for it, then use it as another table in the main query and join with the grouping key and the aggregate value for each key.
Is this the standard SQL way of doing it? Is it any quicker than using a temporary table, or is in fact using a temporary table interanlly anyway?
