On Wed, 14 Mar 2001, G. Anthony Reina wrote:
> I think it would be something like:
> select distinct subject, arm, rep from circles_proc where rep = (select
> rep from circles_proc where 5 = count(cycle));
You're heading towards
SELECT distinct subject, arm, rep FROM circles_proc WHERE rep IN
(SELECT rep FROM circles_proc GROUP BY rep HAVING COUNT(rep)=5);
A possible variant would be
SELECT distinct subject, arm, rep FROM circles_proc c1 WHERE EXISTS
(SELECT rep FROM circles_proc c2 WHERE c1.rep=c2.rep GROUP BY reg HAVING
though w/a small set of test data, this seems less efficient.
SELECT DISTINCT subj, arm, rep FROM circles_proc c1 WHERE 5=
(SELECT COUNT(rep) FROM circles_proc c2 where c1.rep=c2.rep);
but, again, EXPLAIN thinks this will be less efficient.
Unless you get a great solution elsewhere :-), try indexing the fields
and testing the different queries above.
Joel Burton <jburton(at)scw(dot)org>
Director of Information Systems, Support Center of Washington
In response to
pgsql-novice by date
|Next:||From: G. Anthony Reina||Date: 2001-03-15 00:43:38|
|Subject: Re: SELECT query|
|Previous:||From: David Olbersen||Date: 2001-03-14 22:31:36|
|Subject: Re: Which is faster, create index after many inserts or