A Little Noise

23Aug/063

Order By Puzzle

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 ▼

Comments (3) Trackbacks (2)
  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.

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

  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


Leave a comment