Re: NOT HAVING clause? - Mailing list pgsql-general

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
>