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

Posted by xulochavez on Stack Overflow See other posts from Stack Overflow or by xulochavez
Published on 2010-05-19T11:02:41Z Indexed on 2010/05/19 11:20 UTC
Read the original article Hit count: 140

Filed under:

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?

© Stack Overflow or respective owner

Related posts about sql