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]
