You're right, but only if there's no GROUP BY. As soon as you use a
GROUP BY _and_ the mentioned WHERE clause, the result will be what the
OP wanted... or you could use SELECT DISTINCT for what he wanted.
On Tue, 2006-01-24 at 15:02, Will Glynn wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend