From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: [HACKERS] Using aggregate in HAVING |
Date: | 1999-12-29 20:57:44 |
Message-ID: | 386A75C8.2CDF0F58@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Bruce Momjian wrote:
> I have finished the aggregate chapter, and am doing the subselect
> chapter. I thought using a subselect for this example would be great,
> but then I thought, "Gee, why can't HAVING do that?" However, I am
> realizing that HAVING can't because without a GROUP BY, it applies to
> all rows as a whole, and there is no meaningful GROUP BY for this case.
>
> My subquery figure actually will show how HAVING fails, and how
> subqueries allow this. Now, I am just asking for confirmation that this
> is true.
>
Well...actually, you can use a self-join like so:
SELECT f1.lastname, f1.firstname, f1.age, avg(f2.age)
FROM friends f1, friends f2
WHERE true
GROUP BY f1.lastname, f1.firstname, f1.age
HAVING f1.age > avg(f2.age);
I don't think you'll be able to state that subselects allow for
queries that HAVING won't. Proving a negative can be very
difficult (although I think you're probably right).
Mike Mascari
From | Date | Subject | |
---|---|---|---|
Next Message | Adriaan Joubert | 1999-12-29 21:00:08 | Re: [HACKERS] Index corruption |
Previous Message | Bruce Momjian | 1999-12-29 20:25:27 | Re: [HACKERS] Using aggregate in HAVING |