In article <20061003075437.38014.qmail@xxxxxxxxxxxxxxxxxxxxxxx>,
Allan Kamau <kamauallan@xxxxxxxxx> 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
