Paul Makepeace ;-)

December 2, 2005

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
Comments

Hmm, come to think of it, they didn't ask any SQL questions. Pity, they're more fun than shell syntax!

Posted by: Paul Makepeace at December 2, 2005 16:30
Post a comment









Remember personal info?