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

Re: Aggregate

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: David Wheeler <david(at)kineticode(dot)com>
Cc: <sfpug(at)postgresql(dot)org>
Subject: Re: Aggregate
Date: 2003-03-03 21:06:29
Message-ID: 20030303130041.V41677-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: sfpug
On Mon, 3 Mar 2003, David Wheeler wrote:

> I need a little advice from the SQL pros on the list. I have this query
> in Bricolage:
>
>           SELECT DISTINCT a.id
>           FROM   site a, member m, site_member c, member m3, member m4,
>                  site_member c3, user_member um,grp_priv gp,
>                  grp_priv__grp_member gm
>           WHERE  a.id = c.object_id
>                  AND c.member__id    = m.id
>                  AND m.active        = 1
>                  AND a.id            = c3.object_id
>                  AND c3.member__id   = m3.id
>                  AND m3.active       = 1
>                  AND m3.grp__id      = gm.grp__id
>                  AND gm.grp_priv__id = gp.id
>                  AND gp.value        < 4
>                  AND gp.grp__id      = m4.grp__id
>                  AND m4.id           = um.member__id
>                  AND um.object_id    = 1
>           ORDER BY a.id
>

> The question is, how can I alter the first query above to get that same
> behavior -- preferably while avoiding the performance overhead of a NOT
> IN statement?

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

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;

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;


In response to

  • Aggregate at 2003-03-03 20:45:00 from David Wheeler

Responses

sfpug by date

Next:From: David FetterDate: 2003-03-03 21:07:51
Subject: Re: Aggregate
Previous:From: David WheelerDate: 2003-03-03 20:45:00
Subject: Aggregate

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