Re: is a 'pairwise' possible / feasible in SQL?

On mán, 2008-08-04 at 17:00 -0400, Rajarshi Guha wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> On Aug 4, 2008, at 4:55 PM, Francisco Reyes wrote:
> > On 2:08 pm 08/04/08 Rajarshi Guha <rguha(at)indiana(dot)edu> wrote:
> >> pair count
> >> - ---- -----
> >> 123 & 456 1
> >> 667 & 879 2
> >
> <snip>
>
> > 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;
> > ac | bc | count
> > -----+-----+-------
> > 123 | 456 | 1
> > 123 | 667 | 1
> > ...
> > Is that what you are looking for?
>
> Thanks a lot - this is very close. Ideally, I'd want unique pairs, so
> the row
>
> 879 | 999 | 1
>
> is the same as
>
> 999 | 879 | 1
>
> Can these duplicates be avoided?

just add a ac<bc condition:
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 AND a.cid < b.cid
group by a.cid, b.cid
order by a.cid;

gnari