Обсуждение: NOT HAVING clause?
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...
I was hoping to write a query rather short by using a "NOT HAVING"
clause. The documentation didn't specify that, and trying it resulted in
a syntax error indeed...
My data consists of a series of images related to an object. There
should be at least one image per object with sort_order = 1. I want to
find all objects that don't match this criterium.
I have these tables (clipped a bit):
CREATE TABLE image (
image_id SERIAL PRIMARY KEY,
object_id INTEGER NOT NULL REFERENCES object MATCH FULL,
sort_order SMALLINT NOT NULL DEFAULT 1
);
CREATE TABLE object (
object_id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
This is what I want, but isn't a valid query:
SELECT object_id
FROM image
GROUP BY object_id
NOT HAVING sort_order = 1;
It is wonderfully short, one of the reasons I like this.
I could write this as:
SELECT object_id
FROM object
WHERE NOT EXISTS (
SELECT object_id
FROM image
WHERE sort_order = 1
AND object_id = object.object_id
);
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.
What is the general opinion on this from the developers? Did I just have
one of those wild and ridiculous ideas? :P
Regards,
--
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//
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. On 1/24/06, Alban Hertroys <alban@magproductions.nl> 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... > > I was hoping to write a query rather short by using a "NOT HAVING" > clause. The documentation didn't specify that, and trying it resulted in > a syntax error indeed... > > My data consists of a series of images related to an object. There > should be at least one image per object with sort_order = 1. I want to > find all objects that don't match this criterium. > > I have these tables (clipped a bit): > CREATE TABLE image ( > image_id SERIAL PRIMARY KEY, > object_id INTEGER NOT NULL REFERENCES object MATCH FULL, > sort_order SMALLINT NOT NULL DEFAULT 1 > ); > > CREATE TABLE object ( > object_id SERIAL PRIMARY KEY, > name TEXT NOT NULL > ); > > This is what I want, but isn't a valid query: > > SELECT object_id > FROM image > GROUP BY object_id > NOT HAVING sort_order = 1; > > It is wonderfully short, one of the reasons I like this. > > I could write this as: > > SELECT object_id > FROM object > WHERE NOT EXISTS ( > SELECT object_id > FROM image > WHERE sort_order = 1 > AND object_id = object.object_id > ); > > 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. > > What is the general opinion on this from the developers? Did I just have > one of those wild and ridiculous ideas? :P > > Regards, > > -- > 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// > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
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. On Tue, 2006-01-24 at 13:51, 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 > > 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 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. On Tue, 2006-01-24 at 15:02, Will Glynn wrote: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
OK, I really didn't think it through, GROUP BY or DISTINCT won't help here. Sorry for the noise. Cheers, Csaba. On Tue, 2006-01-24 at 15:11, Csaba Nagy wrote: > 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. > > On Tue, 2006-01-24 at 15:02, Will Glynn wrote: > > 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 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: explain analyze is your friend > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
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//