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.
On Tue, 2006-01-24 at 13:51, Alban Hertroys wrote:
> Michael Glaesemann wrote:
> >
> > On Jan 24, 2006, at 20:00 , Alban Hertroys wrote:
> >
> >> Though this does give the right results, I would have liked to be
> >> able to use NOT HAVING. Or is there a way using HAVING that would
> >> give the same results? I'm quite sure HAVING sort_order <> 1 doesn't
> >> mean the same thing.
> >
> > Why are you so sure? It seems to me that NOT HAVING sort_order = 1 and
> > HAVING sort_order <> 1 would mean semantically the same thing. Can you
> > show that HAVING sort_order <> 1 gives incorrect results?
>
> There's a difference in meaning. By NOT HAVING sort_order = 1 I mean
> there is no record in the grouped records that has sort_order = 1. In
> contrast HAVING sort_order <> 1 means there is a record in the group
> with a sort_order other than 1, even if there's also a sort_order = 1 in
> the grouped records.
>
> To illustrate, say we have sort_orders 1,2,3,4,5:
> - NOT HAVING sort_order = 1 would result false
> - HAVING sort_order <> 1 would result true
>
> If we'd have 2,3,4,5:
> - NOT HAVING sort_order = 1 would result true
> - HAVING sort_order <> 1 would result true
>
> If we'd have 1 only:
> - NOT HAVING sort_order = 1 would result false
> - HAVING sort_order <> 1 would result false
>
> But it seems HAVING can't be applied to columns not in the group by or
> an aggregate. No idea why that might be...