-----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@xxxxxxxxxxx> 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@xxxxxxxxxxx>
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-----
