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
