A Little Noise

August 23, 2006

Order By Puzzle

Filed under: MySQL Gotchas — snoyes @ 2:30 pm

From a question on Freenode.

SELECT * FROM theTable;
+----+----------+
| id | data     |
+----+----------+
|  1 | middle   |
|  2 | first    |
|  3 | showLast |
+----+----------+

Fair enough. Let’s order it by `data`.

SELECT * FROM theTable ORDER BY data;
+----+----------+
| id | data     |
+----+----------+
|  3 | showLast |
|  1 | middle   |
|  2 | first    |
+----+----------+

What??? Maybe the server didn’t quite understand.

SELECT * FROM theTable ORDER BY data ASC;
+----+----------+
| id | data     |
+----+----------+
|  3 | showLast |
|  1 | middle   |
|  2 | first    |
+----+----------+

How did that happen? I assure you I have not switched to some ‘reverse order’ collation.

Show Answer ▼

5 Comments »

  1. One good reason not to use SET and ENUM, besides the point that the data requires DML statements and security to manage.

    I must admit, when I read your post I was initally stumped.

    Comment by Ronald Bradford — August 24, 2006 @ 12:59 am

  2. I was stumped too. Just one of many reasons why not to use ENUM/SET… :)

    Comment by Jay Pipes — August 24, 2006 @ 8:31 pm

  3. There may be good reasons not to use ENUM, but this behavior isn’t one of them.

    Ronald, you claim that if you don’t know your data, you can’t make good design decisions about your database*. Surely the converse is true – if you don’t know your database, you have no business writing queries against it. That’s the job of an application that knows all about the structure and can properly escape and validate and convert and order results. Otherwise, we force our nice declarative SQL back into a procedural paradigm.

    One thing I do know is that PROCEDURE ANALYSE() sure is fond of enums.

    *http://blog.arabx.com.au/?p=310

    Comment by Scott Noyes — August 25, 2006 @ 1:31 pm

  4. Enigme en mysql

    L’énigme en français

    Trackback by Kamelot Blog — August 25, 2006 @ 7:48 pm

  5. […] short gotchas with MySQL stuff this week: the first, a sneaky ORDER BY quiz; the second involving an auto_increment puzzle. Go and see if you need to click his “Show Answer” […]

    Pingback by Log Buffer #7: A Carnival of the Vanities for DBAs — February 1, 2013 @ 10:51 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress