List:General Discussion« Previous MessageNext Message »
From:BMBasal Date:September 29 2010 10:49pm
Subject:RE: ORDER BY with field alias issue
View as plain text  
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
throw-away, say, 
"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.

-----Original Message-----
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`
FROM `reservation`
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?


Chris W

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
ORDER BY with field alias issueChris W29 Sep
  • Re: ORDER BY with field alias issueJohnny Withers29 Sep
  • RE: ORDER BY with field alias issueBMBasal30 Sep
    • RE: ORDER BY with field alias issueDaevid Vincent30 Sep