MySQL seems to have a problem with using a function as an ORDER BY parameter
If you move the SELECT query into a Aliased subselect
and perform the ORDER BY outside the Aliased subselect
that should produced the desired result
select * from
(select right(concat('000',roomno),3) AS text,firstname,lastname from
test.names) A
order by text,lastname,firstname;
----- Original Message -----
From: "Jesse" <[EMAIL PROTECTED]>
To: "Zhaowei" <[EMAIL PROTECTED]>, mysql@lists.mysql.com
Sent: Friday, March 23, 2007 11:23:21 AM (GMT-0500) Auto-Detected
Subject: Re: Not Sorting Correctly
Strange. I'm running the same exact version, and it's not the same. What
field types are you using? Mine are as follows:
RoomNo VarChar(10)
LastName VarChar(25)
FirstName VarChar(25)
the values that I put into Room No are "1","2","3", etc. I'm not storing
"001","002","003", etc in there.
Jesse
----- Original Message -----
From: "Zhaowei" <[EMAIL PROTECTED]>
To: "Jesse" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
Sent: Friday, March 23, 2007 5:03 AM
Subject: Re: Not Sorting Correctly
> Hi, Jesse,
>
> I did a small test and found it was in order. My version is
> +-------------------------+
> | version() |
> +-------------------------+
> | 5.0.22-community-nt-log |
> +-------------------------+
>
> select right(concat('000',text_id),3) AS text,name from
> an order by right(concat('000',text_id),3),name;
> +------+---------+
> | text | name |
> +------+---------+
> | 001 | cat |
> | 001 | dog |
> | 001 | monkey |
> | 001 | rat |
> | 001 | wolf |
> | 002 | cat |
> | 002 | whale |
> | 003 | lax |
> | 003 | penguin |
> | 006 | ostrich |
> +------+---------+
> 10 rows in set (0.00 sec)
>
>
> On 3/23/07, Jesse <[EMAIL PROTECTED]> wrote:
>> When I run the following query:
>>
>> SELECT RIGHT(CONCAT('000',RoomNo),3),LastName,FirstName
>> FROM ConfHotelDet
>> WHERE ChapterID=358 AND RoomNo IS NOT NULL
>> ORDER BY RIGHT(CONCAT('000',RoomNo),3), LastName, FirstName
>>
>> I get the following result:
>>
>> 001 Anderson Kayla
>> 002 Barton Greg
>> 003 Beaty Brooke
>> 001 Brown Paige
>> 002 Bynum Wesley
>> 008 Clark Andrew
>> 008 Clark Ramsey
>> Etc...
>>
>> As you can see, it's out of order.
>>
>> Jesse
>>
>> ----- Original Message -----
>> From: "Ales Zoulek" <[EMAIL PROTECTED]>
>> To: "Jesse" <[EMAIL PROTECTED]>
>> Cc: "MySQL List" <mysql@lists.mysql.com>
>> Sent: Monday, March 19, 2007 9:06 PM
>> Subject: Re: Not Sorting Correctly
>>
>>
>> > pls, post result of:
>> >
>> > SELECT RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName FROM....
>> >
>> > Ales
>> >
>> >
>> >
>> > On 3/19/07, Jesse <[EMAIL PROTECTED]> wrote:
>> >> I have an app that I've converted to MySQL from MS SQL. I used to use
>> >> the
>> >> following to force a Alpha field to sort as if it were numeric (I
>> >> know,
>> >> perhaps it is better if I made the field numeric to begin with, but
>> >> it's
>> >> not, and I don't remember why, but that's not the question here):
>> >>
>> >> ORDER BY RIGHT('000' + RoomNo,3),LastName,FirstName
>> >>
>> >> I converted this to the following in MySQL:
>> >>
>> >> ORDER BY RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName
>> >>
>> >> In MS SQL, it would sort correctly:
>> >>
>> >> 1 Kayla Andre
>> >> 1 Paige Brackon
>> >> 1 Kasie Guesswho
>> >> 1 Katelyn Hurst
>> >> 2 Craig Bartson
>> >> 2 Wesley Bytell
>> >> 2 Kevin Peterson
>> >> 2 Bryan Wilton
>> >> etc...
>> >>
>> >> Now, the Above (RIGHT(CONCAT...)-MySQL Version), seems to ignore the
>> >> first
>> >> sort "field", and simply sorts alphabatically:
>> >> 1 Kayla Andre
>> >> 2 Craig Bartson
>> >> 1 Paige Brackon
>> >> 2 Wesley Bytell
>> >> 1 Kasie Guesswho
>> >> 1 Katelyn Hurst
>> >> 2 Kevin Peterson
>> >> 2 Bryan Wilton
>> >>
>> >> I finally ended up with:
>> >>
>> >> ORDER BY CAST(RoomNo AS UNSIGNED), LastName, FirstName
>> >>
>> >> Which works perfectly, but I'm just wondering why the first attempt
>> >> (right(concat...)) didn't work?? Any ideas?
>> >>
>> >> Thanks,
>> >> Jesse
>> >>
>> >> --
>> >> MySQL General Mailing List
>> >> For list archives: http://lists.mysql.com/mysql
>> >> To unsubscribe:
>> >> http://lists.mysql.com/[EMAIL PROTECTED]
>> >>
>> >>
>> >
>> >
>> > --
>> > ------------------------------------------------------
>> > Ales Zoulek
>> > NetCentrum s.r.o.
>> > +420 739 542 789
>> > +420 604 332 515
>> > ICQ: 82647256
>> > ------------------------------------------------------
>> >
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/[EMAIL PROTECTED]
>>
>>
>
>
> --
> Best Regards,
>
> Yours Zhaowei
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
