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

Rajarshi Guha wrote
>
> 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
>> 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?

Depends on values and other distinguishing attributes....
For the given example - assuming pairing of a given cid with itself is not to be expected:
add a "and a.cid < b.cid" to the query....

Rainer
>
> -------------------------------------------------------------------
> Rajarshi Guha <rguha(at)indiana(dot)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.
>
>