make an alias for the field you want as sort key and use that. you
don't need to do the calculation twice. I would not be surprised if
the sort started to behave.
SELECT RIGHT(CONCAT('000',RoomNo),3) AS theroom,LastName,FirstName
FROM ConfHotelDet
WHERE ChapterID=358 AND RoomNo IS NOT NULL
ORDER BY theroom, LastName, FirstName
On 3/22/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]
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
