SQL Friday fun

Posted in: Tech

After Tuesday's Google interview wherein I was asked all sorts of tricky little questions, here's one of my own.

I'm currently reviewing a dataset to see whether I can implement a UNIQUE index on one of the columns. This is pretty straightforward. I want to join a table to itself where the values are the same but the primary key is different:

select ct1.uid, ct2. uid, ct1.value from code_tree ct1 join code_tree ct2 on ct1.value = ct2.value and ct1.uid != ct2.uid;

+------+------+-----------+

| uid  | uid  | value     |

+------+------+-----------+

|  190 |  184 | bars      |

|  184 |  190 | bars      |

| 1333 | 1283 | Up        |

| 1334 | 1284 | Down      |

| 1335 | 1293 | Unchanged |

| 1283 | 1333 | Up        |

| 1284 | 1334 | Down      |

| 1293 | 1335 | Unchanged |

+------+------+-----------+

Can you see the problem? What's the least change required to Do The Right Thing?

Answer below:

Simply change the != to a > sign:

select ct1.uid, ct2. uid, ct1.value from code_tree ct1 join code_tree ct2 on ct1.value = ct2.value and ct1.uid > ct2.uid;
+------+------+-----------+
| uid  | uid  | value     |
+------+------+-----------+
|  190 |  184 | bars      |
| 1333 | 1283 | Up        |
| 1334 | 1284 | Down      |
| 1335 | 1293 | Unchanged |
+------+------+-----------+

(I picked > rather than < so I didn't have to escape the HTML entities in this blog post ;-)

PS Sorry about the line spacing above; I think it's a Movable Type bug.

Posted by Paul Makepeace at December 2, 2005 15:48 | TrackBack