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
