From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [HACKERS] correlated subquery |
Date: | 1999-12-29 23:18:07 |
Message-ID: | 5329.946509487@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Is this a good example of a required correlated subquery:
> SELECT f1.firstname, f1.lastname, f1.age
> FROM friends f1
> WHERE age = (
> SELECT MAX(age)
> FROM friends f2
> WHERE f1.state = f2.state
> )
> ORDER BY firstname, lastname
> It finds the oldest person in each state. HAVING can't do that, right?
Yes, I think you are right. You could find the oldest age in each state
with a HAVING:
SELECT state, MAX(age) FROM friends GROUP BY state;
but I don't see any way to get the other attributes of the record(s)
matching that age, except by using a subselect.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 1999-12-29 23:27:16 | Re: [HACKERS] Index corruption |
Previous Message | Adriaan Joubert | 1999-12-29 23:10:14 | Re: [HACKERS] Index corruption |