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:4rfv...@cox.net] 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=li...@cs.albany.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: ORDER BY with field alias issue
Easy. SELECT DATE_FORMAT(`Time`, '%h:%i%p') as `Time_Format` FROM `reservation` ORDER BY `Time` -Original Message- From: BMBasal [mailto:bmb37...@gmail.com] 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:4rfv...@cox.net] 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=li...@cs.albany.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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=arch...@jab.org
Re: ORDER BY with field alias issue
Order by reservation.time JW On Tuesday, September 28, 2010, Chris W 4rfv...@cox.net wrote: 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=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
