Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA87519 for ; Wed, 29 Sep 1999 19:23:04 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id TAA05279; Wed, 29 Sep 1999 19:22:29 -0400 (EDT) To: Stuart Rison cc: pgsql-sql Subject: Re: [SQL] self-join and DISTINCT quandry. In-reply-to: Your message of Wed, 29 Sep 1999 21:11:24 +0100 (BST) Date: Wed, 29 Sep 1999 19:22:29 -0400 Message-ID: <5276.938647349@sss.pgh.pa.us> From: Tom Lane Stuart Rison writes: > But what is I didn't want the information to appear twice. I could try > adding DISTINCT but that would not work because row a,1,7 is of course > DISTINCT from a,7,1 although I am looking for such duplication to be > eliminated... Bit of a hack, but you could add the condition ... AND t1.number < t2.number; to get rid of the two-way duplication that otherwise arises from your join. (Any other way of selecting just one of the pair of matches would work as well, of course; you could compare the two row's OIDs if nothing else was handy.) Not sure if you still need DISTINCT after that. regards, tom lane