views:

41

answers:

2

I have the following SQL table:

A|B
---
w|x
x|w
y|z
z|y

Can I construct a query which will yield the following result:

A|B
---
w|x
y|z

To summarize, I would like to treat the two columns as an unordered set, such that (a,b) == (b,a).

+1  A: 

You could try the following:

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

With the following test-table t:

CREATE TABLE t ( a VARCHAR(1), b VARCHAR(1) );

INSERT INTO t VALUES ('w','x'),('x','w'),('y','z'),('z','y');

it returns:

w  x
y  z

Using LEAST and GREATEST also makes sure that w x is returned instead of x w.

Peter Lang
Didn't know about `least` and `greatest`. Nice. Wouldn't `distinct` work here as well?
Kobi
Yes, `DISTINCT` would return the same in this case. You would have to test performance, but I guess it would be the same.
Peter Lang
Very cool. Nice solution.
CJH
+2  A: 

The "best" code depends on the database, but following is dbms-agnostic:

SELECT      t.A,
            t.B
FROM        my_table t
LEFT JOIN   my_table t2
        ON  t.A = t2.B
        AND t.B = t2.A
        AND t.A < t.B
WHERE       t2.A IS NULL
van
+1: I was about to edit my answer and add this one too :) Use `t.A > t.B` though, to get the specified results (`w|x` instead of `x|w`).
Peter Lang
Damn. I was so close to having this, I left out the ordering comparison and so my results were always nil.
CJH
I'd say `w<x` and `y<z`, but what do I know :)
Peter Lang
@Peter: right - guess I need another coffee :)
van