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

Re: SELECT query

From: Joel Burton <jburton(at)scw(dot)org>
To: "G(dot) Anthony Reina" <reina(at)nsi(dot)edu>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: SELECT query
Date: 2001-03-15 00:35:18
Message-ID: Pine.LNX.4.21.0103141928340.17320-100000@olympus.scw.org (view raw or flat)
Thread:
Lists: pgsql-novice
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
   COUNT(rep)=5)

though w/a small set of test data, this seems less efficient.

There's also

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.

Good luck,

-- 
Joel Burton   <jburton(at)scw(dot)org>
Director of Information Systems, Support Center of Washington



In response to

Responses

pgsql-novice by date

Next:From: G. Anthony ReinaDate: 2001-03-15 00:43:38
Subject: Re: SELECT query
Previous:From: David OlbersenDate: 2001-03-14 22:31:36
Subject: Re: Which is faster, create index after many inserts or before?

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