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.
August 24th, 2006 - 00:59
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.
August 24th, 2006 - 20:31
I was stumped too. Just one of many reasons why not to use ENUM/SET… :)
August 25th, 2006 - 13:31
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