Re: NOT HAVING clause?

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.

No, you're wrong in both cases there (or would be if NOT HAVING was legal).

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.

So, you can refer to HAVING MAX(sort_order) > 10 for example, but not
HAVING sort_order of anything (because you don't group by it or apply an
aggregate function to it).

> But it seems HAVING can't be applied to columns not in the group by or
> an aggregate. No idea why that might be...

See above. You're not the only person to be confused by HAVING. I'd have
left it out altogether and relied on doing the aggregation in a
sub-query and applying another WHERE to its output.

--
Richard Huxton
Archonet Ltd