From: Daevid Vincent
Date: September 29 2010 11:29pm
Subject: RE: ORDER BY with field alias issue
List-Archive: http://lists.mysql.com/mysql/223194
Message-Id: <2C7EEED925C44E32AFA91A09EA3E0331@mascorp.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Easy.

SELECT DATE_FORMAT(`Time`, '%h:%i%p') as `Time_Format`
FROM `reservation`
ORDER BY `Time`

> -----Original Message-----
> From: BMBasal [mailto:bmb37542@stripped] 
> Sent: Wednesday, September 29, 2010 3:50 PM
> To: 'Chris W'; 'MYSQL General List'
> Subject: RE: ORDER BY with field alias issue
> 
> 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=linyu@stripped
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=daevid@stripped
>