Ok, someone came in #oracle on freenode and wanted a query that would give him a distinct set of rows, where it did not matter if a values is in the first or second column.

Here is a sample table:

CREATE TABLE t (
  a int(11) NOT NULL,
  b int(11) NOT NULL
);

INSERT INTO t (a, b) VALUES (1, 2);
INSERT INTO t (a, b) VALUES (2, 1);
INSERT INTO t (a, b) VALUES (3, 4);
INSERT INTO t (a, b) VALUES (3, 6);
INSERT INTO t (a, b) VALUES (4, 3);
INSERT INTO t (a, b) VALUES (6, 3);

The output should be something like:

(1, 2), (3, 4), (3, 6)
OR
(2, 1), (3, 4), (3, 6)
OR
(6, 3), (2, 1), (3, 4)
etc ..

You get the drift ..

A self join solves the challenge, though we were wondering if any RDBMS out there could do it without a self-join by just using funky analytic features. Any RDBMS specific magic goes (except for implementing a join inside a stored procecure of course). Lets see what MySQL, Oracle, PostgreSQL and friends have to offer!

Lets get ready to rumble!

UPDATE: Also check out Hali's variant of the challenge

Possible solution in Oracle:

select distinct a, b from
( select a, b from t where a >= b
union all
select b, a from tt where b > a
)

a possible mysql solution:

SELECT DISTINCT newt.frst, newt.scnd
FROM (
SELECT LEAST(a,b) AS frst,
GREATEST(a, b) AS scnd
FROM t
) AS newt

it should also work with postgresql >= 8.1

Another solution valid on any dbms having the "sign" function and the "CASE" construct:

SELECT DISTINCT newt.frst, newt.scnd
FROM (
SELECT
CASE sign(b - a) WHEN 1 THEN a ELSE b END AS frst,
CASE sign(a - b) WHEN 1 THEN a ELSE b END AS scnd
FROM t
) AS newt

Also in Oracle:

select distinct a, b from
(select least(a,b) as a, greatest(a,b) as b from t)

ahem, the "sign" function is totally unnecessary:

SELECT DISTINCT newt.frst, newt.scnd
FROM (
SELECT
CASE WHEN b > a THEN a ELSE b END AS frst,
CASE WHEN b > a THEN b ELSE a END AS scnd
FROM t
) AS newt

It works in mysql (tested: v.5.1), postgresql (tested: v.8.0).
Should work in fyracle and firebird 2.0.

mysql> use test;
Database changed
mysql> create table contest (a int, b int);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into contest values (1,2),(2,1),(3,4),(3,6),(4,3),(6,3);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select distinct least(a,b), greatest(a,b)
-> from contest;
+------------+---------------+
| least(a,b) | greatest(a,b) |
+------------+---------------+
| 1 | 2 |
| 3 | 4 |
| 3 | 6 |
+------------+---------------+
3 rows in set (0.03 sec)

yeah, as soon as I shut the PC down, yesterday night, it occurred to me that the subquery was unnecessary too...

SELECT DISTINCT LEAST(a,b), GREATEST(a,b) FROM t;

-- or

SELECT DISTINCT
CASE WHEN b > a THEN a ELSE b END,
CASE WHEN b > a THEN b ELSE a END
FROM t;

these queries also lower the requirements on the dbms version.

Assuming there's a third column `c` and you want one value from that column too, you might do something like this:

SELECT a, b, MIN(c) FROM t GROUP BY LEAST(a, b), GREATEST(a, b);