NOT HAVING clause?

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?

Michael Glaesemann
grzm myrealbox com

Hi,

SELECT object_id
FROM image
GROUP BY object_id
NOT HAVING sort_order = 1;

After changing the "NOT HAVING" to "HAVING" the error message was
"column "sort_order" must appear in the GROUP BY clause or be used in
an aggregate function"

The postgres document says "SELECT list and HAVING clause can only
reference table columns from within aggregate functions"

IMHO, I feel that it is not just postgres which cant do, but no other
database because i dont see any meaningful way of fetching rows for
the query given above.

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

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

//Showing your Vision to the World//

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.

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

Richard Huxton wrote:

Alban Hertroys wrote:
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.

Ah, of course, now it makes sense. Combined with Csaba's reply my
original problem has vaporized. Thank you guys :)

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

//Showing your Vision to the World//

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

Alban Hertroys wrote:

Richard Huxton wrote:

Alban Hertroys wrote:
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.

Ah, of course, now it makes sense. Combined with Csaba's reply my
original problem has vaporized. Thank you guys :)

Csaba's response is incorrect:

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.

If you do that, the query reads "give me unique values for some_column
from some_table, ignoring individual records that have sort_order=1".

To illustrate, say we have sort_orders 2,3,4,5:
- NOT HAVING sort_order = 1 would result true
- HAVING sort_order <> 1 would result true
- WHERE sort_order <> 1 would result true for all records

If we'd have 1 only:
- NOT HAVING sort_order = 1 would result false
- HAVING sort_order <> 1 would result false
- WHERE sort_order <> 1 would result false

If we'd have 1,2,3,4,5:
- NOT HAVING sort_order = 1 would result false
- HAVING sort_order <> 1 would result true
- WHERE sort_order <> 1 would result true for records 2,3,4,5, returning
some_column anyway, which is not what you want

This can be done with an aggregate, a sub-select, or a JOIN -- there's
no way to do this using only a single-table WHERE.

--Will Glynn
Freedom Healthcare

You're right, but only if there's no GROUP BY. As soon as you use a
GROUP BY _and_ the mentioned WHERE clause, the result will be what the
OP wanted... or you could use SELECT DISTINCT for what he wanted.

OK, I really didn't think it through, GROUP BY or DISTINCT won't help
here. Sorry for the noise.

Cheers,
Csaba.

On Tue, 24 Jan 2006, Alban Hertroys wrote:

This is sort of a feature request, I suppose. I solved my problem, but
"NOT HAVING" seems to match better with the desired result or the way
you phrase the question in your mind, if that makes any sense...

One problem is that HAVING really works on entire groups at a time
(including aggregated data for the group) not on pieces of the group.

However, I think one might be able to fake it with an array accumulating
aggregate like the one from
http://www.postgresql.org/docs/current/static/xaggr.html

and a query like:

SELECT object_id FROM image GROUP BY object_id HAVING
NOT(1 = ANY(array_accum(sort_order))).

On 2006-01-24, Will Glynn <wglynn@freedomhealthcare.org> wrote:

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;

SELECT some_column
FROM some_table
GROUP BY some_column
HAVING every(sort_order <> 1);

every() is in 8.1 at least (can't recall when it was introduced); it's the
same as bool_and(), i.e. an aggregate that returns true only if all inputs
are true. Why isn't there a corresponding any(), I wonder? (bool_or does
exist)

It should be possible to define bool_and() or every() in the usual way as
a custom aggregate even on versions that don't have them builtin.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

Andrew - Supernews wrote:

On 2006-01-24, Will Glynn <wglynn@freedomhealthcare.org> wrote:

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;

SELECT some_column
FROM some_table
GROUP BY some_column
HAVING every(sort_order <> 1);

every() is in 8.1 at least (can't recall when it was introduced); it's the
same as bool_and(), i.e. an aggregate that returns true only if all inputs
are true. Why isn't there a corresponding any(), I wonder? (bool_or does
exist)

Unfortunately we still use 7.4, but I realized this morning that this
should work too (not tried yet):

SELECT some_column
FROM some_table
GROUP BY some_column
HAVING MIN(sort_order) > 1;

As our sort_orders start from 1.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

//Showing your Vision to the World//