SQL to get rows (not groups) that match an aggregate

Question

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?

Answer 1

What you are using will work, although it displays all users which share the max age.

You can do this in a slightly more readable way using the row_number() ranking function:

select name, city, age 
from (
    select 
        city
     ,  age
     ,  row_number() over (partition by city order by age) as rn
     from USER
) sub
where rn = 1

This will also select at most one user per city.

Most database systems will use a temporary table to store the inner query. So I don't think a temporary table would speed it up. But database performance is notoriously hard to predict from a distance :)