Re: NOT HAVING clause?

OK, I really didn't think it through, GROUP BY or DISTINCT won't help
here. Sorry for the noise.

Cheers,
Csaba.

On Tue, 2006-01-24 at 15:11, Csaba Nagy wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly