From: | "Francisco Reyes" <lists(at)stringsutils(dot)com> |
---|---|
To: | Rajarshi Guha <rguha(at)indiana(dot)edu> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: is a 'pairwise' possible / feasible in SQL? |
Date: | 2008-08-04 20:55:21 |
Message-ID: | 8e5fd35ad41a05901b490d11a5e7f893@stringsutils.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2:08 pm 08/04/08 Rajarshi Guha <rguha(at)indiana(dot)edu> wrote:
> pair count
> - ---- -----
> 123 & 456 1
> 667 & 879 2
create temp table aic_cid
(
id smallint,
cid smallint
);
insert into aic_cid values (1,123);
insert into aic_cid values (2,456);
insert into aic_cid values (3,667);
insert into aic_cid values (3,879);
insert into aic_cid values (3,123);
insert into aic_cid values (4,878);
insert into aic_cid values (4,456);
insert into aic_cid values (4,123);
insert into aic_cid values (5,999);
insert into aic_cid values (5,667);
insert into aic_cid values (5,879);
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Rajarshi Guha | 2008-08-04 21:00:31 | Re: is a 'pairwise' possible / feasible in SQL? |
Previous Message | Rob Adams | 2008-08-04 20:06:41 | Re: recovery via base + WAL replay failure |