Received: from mail-b.bcc.ac.uk (mail-b.bcc.ac.uk [144.82.100.22]) by hub.org (8.9.3/8.9.3) with ESMTP id GAA01028 for ; Thu, 30 Sep 1999 06:03:36 -0400 (EDT) (envelope-from rison@biochemistry.ucl.ac.uk) Received: from biochem.ucl.ac.uk (actually host bsmori1.biochem.ucl.ac.uk) by mail-b.bcc.ac.uk with SMTP (XT-PP) with ESMTP; Thu, 30 Sep 1999 11:03:11 +0100 Received: from bsmlx17.biochem.ucl.ac.uk (bsmlx17 [128.40.46.142]) by biochem.ucl.ac.uk (8.9.2/8.9.1) with ESMTP id LAA06829; Thu, 30 Sep 1999 11:03:17 +0100 (BST) Received: from localhost (rison@localhost) by bsmlx17.biochem.ucl.ac.uk (8.9.3/8.6.6) with ESMTP id LAA00762; Thu, 30 Sep 1999 11:08:35 +0100 X-Authentication-Warning: bsmlx17.biochem.ucl.ac.uk: rison owned process doing -bs Date: Thu, 30 Sep 1999 11:08:35 +0100 (BST) From: Stuart Rison X-Sender: rison@bsmlx17 To: Tom Lane cc: pgsql-sql Subject: Re: [SQL] self-join and DISTINCT quandry. In-Reply-To: <5276.938647349@sss.pgh.pa.us> Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Dear Tom, Thanks a lot, that works great, the simple ideas are always the best! There is one proviso. If the two numbers are equal, the 'hack' does not work. So the trick, as you point out, is to distinguish on oid's (... AND t1.oid 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 > > ************ > Stuart C. G. Rison Department of Biochemistry and Molecular Biology 6th floor, Darwin Building, University College London (UCL) Gower Street, London, WC1E 6BT, United Kingdom Tel. 0207 504 2303, Fax. 0207 380 7033 e-mail: rison@biochem.ucl.ac.uk