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=gcdmg-mysql-2@m.gmane.org

Re: ORDER BY with field alias issue

Order by reservation.time

JW

On Tuesday, September 28, 2010, Chris W <4rfvgy7@cox.net> wrote:
> =A0I 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. =A0I guess I should have named things differently but I would r=
ather not do that. =A0Is there a standard way to get around this and have i=
t sort by the non-formatted time value?
>
>
> Chris W
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Djohnny@pixela=
ted.net
>
>

--=20
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

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@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=linyu@cs.albany.edu


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.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:bmb37542@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:4rfvgy7@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=linyu@cs.albany.edu
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=daevid@daevid.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org