It is inherent in your naming.
As long as your alias "time" is the same as the column name "time", MySQL
will have no way to distinguish which one you refers to exactly in your
order-by clause, and chooses the alias in the select-clause as the one you
intended. You confused MySQL.
First, why you have to hang on "time" as alias.
Second, if you don't mind adding another column in your select-clause as a
"select DATE_FORMAT(`Time`, '%h:%i%p') as `Time`, `time` as `timex`"
Then, you could use `timex` in your order clause. This works, but with extra
output, not elegant.
From: Chris W [mailto:4rfvgy7@stripped]
Sent: Tuesday, September 28, 2010 8:10 PM
To: MYSQL General List
Subject: ORDER BY with field alias issue
I have the following query that is giving me problems.
SELECT DATE_FORMAT(`Time`, '%h:%i%p') as `Time`
ORDER BY `Time`
Problem is it sorts wrong because of the date format function output
with am and pm. I guess I should have named things differently but I
would rather not do that. Is there a standard way to get around this
and have it sort by the non-formatted time value?
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1