From: | Will Glynn <wglynn(at)freedomhealthcare(dot)org> |
---|---|
To: | Alban Hertroys <alban(at)magproductions(dot)nl> |
Cc: | Richard Huxton <dev(at)archonet(dot)com>, Michael Glaesemann <grzm(at)myrealbox(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: NOT HAVING clause? |
Date: | 2006-01-24 14:02:43 |
Message-ID: | 43D63383.5070503@freedomhealthcare.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Alban Hertroys wrote:
> Richard Huxton wrote:
>
>> Alban Hertroys wrote:
>> You're mixing up WHERE and HAVING. The WHERE clause applies to the
>> individual rows before GROUP BY. The HAVING applies to the output of
>> the GROUP BY stage.
>
>
> Ah, of course, now it makes sense. Combined with Csaba's reply my
> original problem has vaporized. Thank you guys :)
Csaba's response is incorrect:
>Alban,
>
>what you want is to put the "sort_order <> 1" in the WHERE clause, not
>in the HAVING clause. Then it will do what you want.
>
>Cheers,
>Csaba.
>
If you do that, the query reads "give me unique values for some_column
from some_table, ignoring individual records that have sort_order=1".
To illustrate, say we have sort_orders 2,3,4,5:
- NOT HAVING sort_order = 1 would result true
- HAVING sort_order <> 1 would result true
- WHERE sort_order <> 1 would result true for all records
If we'd have 1 only:
- NOT HAVING sort_order = 1 would result false
- HAVING sort_order <> 1 would result false
- WHERE sort_order <> 1 would result false
If we'd have 1,2,3,4,5:
- NOT HAVING sort_order = 1 would result false
- HAVING sort_order <> 1 would result true
- WHERE sort_order <> 1 would result true for records 2,3,4,5, returning
some_column anyway, which is not what you want
This can be done with an aggregate, a sub-select, or a JOIN -- there's
no way to do this using only a single-table WHERE.
--Will Glynn
Freedom Healthcare
From | Date | Subject | |
---|---|---|---|
Next Message | Csaba Nagy | 2006-01-24 14:11:58 | Re: NOT HAVING clause? |
Previous Message | Will Glynn | 2006-01-24 13:56:33 | Re: NOT HAVING clause? |