Re: Aggregate

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]