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

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