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