It's possible to repeat with a varbinary field also in 5.1
CREATE TABLE t1 (
RoomNo varbinary(10) DEFAULT NULL,
LastName varchar(25) NOT NULL,
FirstName varchar(25) NOT NULL,
ChapterID int(11) NOT NULL DEFAULT '358'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t1` (`RoomNo`, `LastName`, `FirstName`, `ChapterID`) VALUES
('1', 'Anderson', 'Kayla', 358),
('2', 'Barton', 'Greg', 358),
('3', 'Beaty', 'Brooke', 358),
('1', 'Brown', 'Paige', 358),
('2', 'Bynum', 'Wesley', 358),
('8', 'Clark', 'Andrew', 358),
('8', 'Clark', 'Ramsey', 358);
The solution are I've found are 2, the first convert the field to utf8,
the second to use lpad function instead of concat, this inherently work
(but don't ask why).
-- explicitly convert it to utf8
SELECT CONVERT( (RIGHT(CONCAT('000',RoomNo),3)) USING utf8) AS
PaddedRoomNo, LastName, FirstName
FROM t1
WHERE ChapterID=358 AND RoomNo IS NOT NULL
ORDER BY PaddedRoomNo, LastName, FirstName
-- pad server side (implicitly convert to utf8 ?)
SELECT LPAD(RoomNo,3,'0')AS PaddedRoomNo, LastName, FirstName
FROM t1
WHERE ChapterID=358 AND RoomNo IS NOT NULL
ORDER BY PaddedRoomNo, LastName, FirstName
Jesse ha scritto:
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]
