Skip site navigation (1) Skip section navigation (2)

Re: Aggregate

From: David Wheeler <david(at)kineticode(dot)com>
To: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: <sfpug(at)postgresql(dot)org>
Subject: Re: Aggregate
Date: 2003-03-04 03:20:44
Message-ID: 496796C8-4DF0-11D7-B98B-0003931A964A@kineticode.com (view raw or flat)
Thread:
Lists: sfpug
On Monday, March 3, 2003, at 01:06  PM, Stephan Szabo wrote:

> Well, the first thought is to try NOT EXISTS rather than NOT IN.

I think I'd have to join all the tables again, all the way back to 
um.object_id (since that's what's actually being queried for via a 
DBD::Pg placeholder).

> Other options might be group by/having or distinct on (untested)
>
> select a.id from ...
>  where ...
>  group by a.id
>  having max(gp.value)<4;

Well, that might work for when I just select a.id, but I have another 
version of the query that selects many more columns (to populate an 
object)

   SELCT a.id, a.name, a.description, a.domain_name, a.active,
         m.grp__id
   ...
   ORDER BY a.name, a.id

> select id from
>  (select distinct on (a.id) a.id, gp.value
>   from ...
>   where ... -- [minus the gp.value check]
>   order by a.id, gp.value desc
>  ) foo
>  where value<4;

Um, don't really understand this syntax...

Thanks. I think I might have to go for the EXISTS IN subquery. Maybe 
I'll bring the whole thing to the meeting next Tuesday.

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david(at)kineticode(dot)com                              ICQ: 15726394
                                                Yahoo!: dew7e
                                                Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]


In response to

sfpug by date

Next:From: David WheelerDate: 2003-03-04 03:30:25
Subject: Re: Aggregate
Previous:From: Josh BerkusDate: 2003-03-03 21:33:21
Subject: Re: Aggregate

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group