Re: NOT HAVING clause?

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