Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 8AC3B9FB276 for ; Tue, 3 Oct 2006 11:05:44 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 35025-04 for ; Tue, 3 Oct 2006 11:05:39 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id CDDCE9FA4F4 for ; Tue, 3 Oct 2006 11:05:39 -0300 (ADT) Received: from root by ciao.gmane.org with local (Exim 4.43) id 1GUktK-0002Nm-Qx for pgsql-admin@postgresql.org; Tue, 03 Oct 2006 16:05:03 +0200 Received: from srv.protecting.net ([212.126.218.242]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 03 Oct 2006 16:05:02 +0200 Received: from hf0731x by srv.protecting.net with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 03 Oct 2006 16:05:02 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-admin@postgresql.org From: Harald Fuchs Subject: Re: [SQL Question] Selecting distinct rows having field value pairs on semantics Date: 03 Oct 2006 15:56:49 +0200 Organization: Linux Private Site Lines: 46 Message-ID: References: <20061003075437.38014.qmail@web53507.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII X-Complaints-To: usenet@sea.gmane.org X-Gmane-NNTP-Posting-Host: srv.protecting.net X-Archive: encrypt User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/20.7 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.43 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/13 X-Sequence-Number: 23316 In article <20061003075437.38014.qmail@web53507.mail.yahoo.com>, Allan Kamau writes: > Hi all, > I am looking for a way of selecting records from a > table comprising of pairing fields having unique > semantics, where the pair of values of lets say 'left' > and 'right' and another pair having values of 'right' > and 'left' is considered as duplicates as they have > the same meaning. In the future, please use comp.db.postgresql.sql for questions like that. > Below is my table structure. > create table ppi_edges_tf > ( > tf_id1 char(6)not null, > tf_id2 char(6)not null, > primary key(tf_id1,tf_id2) > ); > I would like to create a query on this table that will > contain only unique [tf_id1 and tf_id2] combination is > unique semantically. > for example looking at a select rows of the table > tf_id1 | tf_id2 > -------+-------- > T00111 | T00111 > T00111 | T00112 > T00111 | T01400 > T00111 | T05015 > T00112 | T00111 > The second record (T00111 | T00112) and the fifth > record(T00112 | T00111) have the same pairing meaning > and should be considered duplicate in my case and only > one for the records (either one) should be contained > in the desired resultset of unique values. You could use something like that: SELECT DISTINCT id1, id2 FROM (SELECT CASE WHEN tf_id1 <= tf_id2 THEN tf_id1 ELSE tf_id2 END AS id1, CASE WHEN tf_id1 >= tf_id2 THEN tf_id1 ELSE tf_id2 END AS id2 FROM ppi_edges_tf ) AS dummy