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]