From: | Will Glynn <wglynn(at)freedomhealthcare(dot)org> |
---|---|
To: | Alban Hertroys <alban(at)magproductions(dot)nl> |
Cc: | Michael Glaesemann <grzm(at)myrealbox(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: NOT HAVING clause? |
Date: | 2006-01-24 13:56:33 |
Message-ID: | 43D63211.9050209@freedomhealthcare.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Will Glynn | 2006-01-24 14:02:43 | Re: NOT HAVING clause? |
Previous Message | Tony Caduto | 2006-01-24 13:56:22 | Re: Does this look ethical to you? |