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

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