Today I was confronted with the following situation: a table with two columns that contained IDs. The content was more or less something like this:
ID ALIAS 1 2 2 1 1 3 2 2
I had to eliminate the records that pointed to themselves (i.e. ID = ALIAS), because it’s a bit silly to say that you’re an alias of yourself.
I also had to eliminate the records that were essentially duplicates. In this example, the record “2, 1″ is functionally the same record as “1, 2″ and thus I didn’t need it.
Could this be solved using SQL only? I suspected it, but couldn’t find a solution fast enough. I called in the help of my colleagues and within minutes Martin Schaap helped me out:
select id, alias from t where id <> alias -- that was the easy part and not exists ( -- here comes the hard part select 1 from t where id = t.alias and alias = t.id and rowid > t.rowid );
UPDATE: one day later, Frank Dorst (yes, that’s right, our “I once was a developer” CTO) also sent me an elegant, working solution:
select distinct least(id, alias), greatest(id, alias) from t where id <> alias;
Kudos to you too, Frank (you still got it ;-))!