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
sql
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