So here is the problem:
Dataset:
1, 2And the output from the query we want is:
2, 1
3, 4
3, 6
4, 3
6, 3
1,2
3,4
3,6
All rows with a matching sister "a=b" relation should be "merged".
Lukas found one quite good way using exists in a self-join.
Any RDBMS goes, ANSI SQL is an advantage but not necessary. Analytics perhaps?
Nicest/fastest/cleanest query wins. Post any questions as comments.
E-mail me or post your solution as a comment to this blog.
See Lukas' invite to the challenge. :)
Thanks and good luck!
2 comments:
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)
SELECT DISTINCT t1.a, t1.b
FROM contest AS t1
JOIN contest AS t2 ON ( t1.a = t2.b )
WHERE t1.a <= t1.b
Post a Comment