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.
>
> 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


You might try:

SELECT some_column
  FROM some_table
  GROUP BY some_column
  HAVING SUM(CASE WHEN sort_order=1 THEN 1 ELSE 0 END) = 0;

That is, "get me values for some_column from some_table; grouping by
some_column, include only groups where the number of grouped records
having sort_order=1 is zero."

--Will Glynn
Freedom Healthcare