Tuesday, October 17

SQL challange #1

Ok, time for a little competition. Myself and Lukas Smith where helping a user with an at first very simple SQL problem. But after a few minutes we wouldn't find a "nice" way to solve it.

So here is the problem:
Dataset:
1, 2
2, 1
3, 4
3, 6
4, 3
6, 3
And the output from the query we want is:
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!
at

2 comments:

  1. rpboumanOctober 18, 2006 3:01 am

    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)

    ReplyDelete
  2. vivoOctober 18, 2006 11:32 am

    SELECT DISTINCT t1.a, t1.b
    FROM contest AS t1
    JOIN contest AS t2 ON ( t1.a = t2.b )
    WHERE t1.a <= t1.b

    ReplyDelete