Re: is a 'pairwise' possible / feasible in SQL? - Mailing list pgsql-general

On Mon, Aug 04, 2008 at 05:00:31PM -0400, Rajarshi Guha wrote:
> On Aug 4, 2008, at 4:55 PM, Francisco Reyes wrote:
> > select a.cid as ac, b.cid as bc, count(*) from aic_cid a left
> >outer join
> >aic_cid b on a.cid <>b.cid and a.id = b.id where b.cid is not null
> >group by
> >a.cid, b.cid order by a.cid;
> >
> >Is that what you are looking for?
>
> Thanks a lot - this is very close. Ideally, I'd want unique pairs

You just need to change the "a.cid <> b.cid" equality to something
non-symmetric, i.e. "a.cid < b.cid".  I'm also not sure why an outer
join is being used.  I've rewritten it to:

  SELECT a.cid AS ac, b.cid AS bc, count(*)
  FROM aic_cid a, aic_cid b
  WHERE a.id = b.id AND a.cid < b.cid
  GROUP BY a.cid, b.cid
  ORDER BY a.cid, b.cid;

and seem to get similar results.


  Sam