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

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