Re: [SQL Question] Selecting distinct rows having field value pairs on semantics

In article <20061003075437(dot)38014(dot)qmail(at)web53507(dot)mail(dot)yahoo(dot)com>,
Allan Kamau <kamauallan(at)yahoo(dot)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