Not Sorting Correctly

#1 (**permalink**) 02-28-2008, 06:02 AM

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

#2 (**permalink**) 02-28-2008, 06:02 AM

pls, post result of:

SELECT RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName FROM....

Ales

On 3/19/07, Jesse <jlc@msdlg.com> 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/mysql?unsub=a...ulek@gmail.com
>
>

--
------------------------------------------------------
Ales Zoulek
NetCentrum s.r.o.
+420 739 542 789
+420 604 332 515
ICQ: 82647256
------------------------------------------------------

#3 (**permalink**) 02-28-2008, 06:03 AM

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" <ales.zoulek@gmail.com>
To: "Jesse" <jlc@msdlg.com>
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 <jlc@msdlg.com> 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/mysql?unsub=a...ulek@gmail.com
>>
>>
>
>
> --
> ------------------------------------------------------
> Ales Zoulek
> NetCentrum s.r.o.
> +420 739 542 789
> +420 604 332 515
> ICQ: 82647256
> ------------------------------------------------------
>

#4 (**permalink**) 02-28-2008, 06:03 AM

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 <jlc@msdlg.com> 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" <ales.zoulek@gmail.com>
> To: "Jesse" <jlc@msdlg.com>
> 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 <jlc@msdlg.com> 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/mysql?unsub=a...ulek@gmail.com
> >>
> >>
> >
> >
> > --
> > ------------------------------------------------------
> > 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/mysql?unsub=mdykman@gmail.com
>
>

--
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.

#5 (**permalink**) 02-28-2008, 06:03 AM

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 <jlc@msdlg.com> 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" <ales.zoulek@gmail.com>
> To: "Jesse" <jlc@msdlg.com>
> 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 <jlc@msdlg.com> 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/mysql?unsub=a...ulek@gmail.com
> >>
> >>
> >
> >
> > --
> > ------------------------------------------------------
> > 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/mysql?unsub=z...teng@gmail.com
>
>

--
Best Regards,

Yours Zhaowei

#6 (**permalink**) 02-28-2008, 06:03 AM

> 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.

Didn't help. The results are exactly the same.

Jesse

#7 (**permalink**) 02-28-2008, 06:03 AM

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" <zhaowei.teng@gmail.com>
To: "Jesse" <jlc@msdlg.com>; <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 <jlc@msdlg.com> 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" <ales.zoulek@gmail.com>
>> To: "Jesse" <jlc@msdlg.com>
>> 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 <jlc@msdlg.com> 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/mysql?unsub=a...ulek@gmail.com
>> >>
>> >>
>> >
>> >
>> > --
>> > ------------------------------------------------------
>> > 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/mysql?unsub=z...teng@gmail.com
>>
>>
>
>
> --
> Best Regards,
>
> Yours Zhaowei
>

#8 (**permalink**) 02-28-2008, 06:04 AM

MySQL seems to have a problem with using a function as an ORDER BY parameter

If you move the SELECT query into a Aliased subselect
and perform the ORDER BY outside the Aliased subselect
that should produced the desired result

select * from
(select right(concat('000',roomno),3) AS text,firstname,lastname from test.names) A
order by text,lastname,firstname;

----- Original Message -----
From: "Jesse" <jlc@msdlg.com>
To: "Zhaowei" <zhaowei.teng@gmail.com>, mysql@lists.mysql.com
Sent: Friday, March 23, 2007 11:23:21 AM (GMT-0500) Auto-Detected
Subject: Re: Not Sorting Correctly

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" <zhaowei.teng@gmail.com>
To: "Jesse" <jlc@msdlg.com>; <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 <jlc@msdlg.com> 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" <ales.zoulek@gmail.com>
>> To: "Jesse" <jlc@msdlg.com>
>> 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 <jlc@msdlg.com> 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/mysql?unsub=a...ulek@gmail.com
>> >>
>> >>
>> >
>> >
>> > --
>> > ------------------------------------------------------
>> > 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/mysql?unsub=z...teng@gmail.com
>>
>>
>
>
> --
> Best Regards,
>
> Yours Zhaowei
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=redwards@swmx.com

#9 (**permalink**) 02-28-2008, 06:04 AM

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" <zhaowei.teng@gmail.com>
> To: "Jesse" <jlc@msdlg.com>; <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 <jlc@msdlg.com> 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" <ales.zoulek@gmail.com>
>>> To: "Jesse" <jlc@msdlg.com>
>>> 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 <jlc@msdlg.com> 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/mysql?unsub=a...ulek@gmail.com
>>> >>
>>> >>
>>> >
>>> >
>>> > --
>>> > ------------------------------------------------------
>>> > 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/mysql?unsub=z...teng@gmail.com
>>>
>>>
>>
>>
>> --
>> Best Regards,
>>
>> Yours Zhaowei
>>
>
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode