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).
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).
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
.
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