-----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@indiana.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
> 123 | 878 | 1
> 123 | 879 | 1
> 456 | 123 | 1
> 456 | 878 | 1
> 667 | 123 | 1
> 667 | 879 | 2
> 667 | 999 | 1
> 878 | 123 | 1
> 878 | 456 | 1
> 879 | 123 | 1
> 879 | 667 | 2
> 879 | 999 | 1
> 999 | 667 | 1
> 999 | 879 | 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?
- -------------------------------------------------------------------
Rajarshi Guha <rguha@indiana.edu>
GPG Fingerprint: D070 5427 CC5B 7938 929C DD13 66A1 922C 51E7 9E84
- -------------------------------------------------------------------
How I wish I were what I was when I wished I were what I am.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (Darwin)
iEYEARECAAYFAkiXbe8ACgkQZqGSLFHnnoRXPACeMcPqXG4QIf308ufnAHev9hlG
EEoAoLzU5tmL1ipiUIp69N9mOvnsfrES
=JOg1
-----END PGP SIGNATURE-----
