From: Harald Fuchs <hf0731x@protecting.net>
To: pgsql-admin@postgresql.org
Subject: Re: [SQL Question] Selecting distinct rows having field value pairs on semantics
Date: 03 Oct 2006 15:56:49 +0200
Message-ID: <puac4dfqou.fsf@srv.protecting.net> (raw)
References: <20061003075437.38014.qmail@web53507.mail.yahoo.com>
In article <20061003075437.38014.qmail@web53507.mail.yahoo.com>,
Allan Kamau <kamauallan@yahoo.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: hf0731x@protecting.net
Subject: Re: [SQL Question] Selecting distinct rows having field value pairs on semantics
In-Reply-To: <puac4dfqou.fsf@srv.protecting.net>
References: <20061003075437.38014.qmail@web53507.mail.yahoo.com> <puac4dfqou.fsf@srv.protecting.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox