Re: Aggregate

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;