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]
