469,934 Members | 1,581 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

ORDER BY

I have this list that i'm ordering by units_unit_id and is there way I can get
a natural order so that D4 and D5 comes before D12

Thanks,
Matt

+---------------+
| units_unit_id |
+---------------+
| D1 |
| D12 |
| D14 |
| D15 |
| D16 |
| D18 |
| D19 |
| D4 |
| D5 |
+---------------+
Jul 20 '05 #1
12 3951
Matias Silva wrote:
I have this list that i'm ordering by units_unit_id and is there way I
can get
a natural order so that D4 and D5 comes before D12


It is not a natural order, natural order is the one you are getting.
Easy way would be to change the table so that D and number would be in
different columns. Another way is to use some string manipulation
functions to parse string into character and a number and then order by
number. This might mean slow queries if you have many rows in your table
so first solution is better if it is possible to do.

left() right() and length() functions atleast are able to to do this.
I'm not sure if there is a better way to do it, you can look for such
functions from the manual:

http://dev.mysql.com/doc/mysql/en/String_functions.html
Jul 20 '05 #2
Matias Silva wrote:
I have this list that i'm ordering by units_unit_id and is there way I
can get
a natural order so that D4 and D5 comes before D12


It is not a natural order, natural order is the one you are getting.
Easy way would be to change the table so that D and number would be in
different columns. Another way is to use some string manipulation
functions to parse string into character and a number and then order by
number. This might mean slow queries if you have many rows in your table
so first solution is better if it is possible to do.

left() right() and length() functions atleast are able to to do this.
I'm not sure if there is a better way to do it, you can look for such
functions from the manual:

http://dev.mysql.com/doc/mysql/en/String_functions.html
Jul 20 '05 #3
Matias Silva wrote:
I have this list that i'm ordering by units_unit_id and is there way I
can get
a natural order so that D4 and D5 comes before D12


It is not a natural order, natural order is the one you are getting.
Easy way would be to change the table so that D and number would be in
different columns. Another way is to use some string manipulation
functions to parse string into character and a number and then order by
number. This might mean slow queries if you have many rows in your table
so first solution is better if it is possible to do.

left() right() and length() functions atleast are able to to do this.
I'm not sure if there is a better way to do it, you can look for such
functions from the manual:

http://dev.mysql.com/doc/mysql/en/String_functions.html
Jul 20 '05 #4
Great point Aggro...
My 2cents...
natural order for me is D01, D02, D03... D12, D13.. etc.

"Aggro" <sp**********@yahoo.com> wrote in message
news:Pe************@read3.inet.fi...
Matias Silva wrote:
I have this list that i'm ordering by units_unit_id and is there way I
can get
a natural order so that D4 and D5 comes before D12


It is not a natural order, natural order is the one you are getting.
Easy way would be to change the table so that D and number would be in
different columns. Another way is to use some string manipulation
functions to parse string into character and a number and then order by
number. This might mean slow queries if you have many rows in your table
so first solution is better if it is possible to do.

left() right() and length() functions atleast are able to to do this.
I'm not sure if there is a better way to do it, you can look for such
functions from the manual:

http://dev.mysql.com/doc/mysql/en/String_functions.html

Jul 20 '05 #5
Great point Aggro...
My 2cents...
natural order for me is D01, D02, D03... D12, D13.. etc.

"Aggro" <sp**********@yahoo.com> wrote in message
news:Pe************@read3.inet.fi...
Matias Silva wrote:
I have this list that i'm ordering by units_unit_id and is there way I
can get
a natural order so that D4 and D5 comes before D12


It is not a natural order, natural order is the one you are getting.
Easy way would be to change the table so that D and number would be in
different columns. Another way is to use some string manipulation
functions to parse string into character and a number and then order by
number. This might mean slow queries if you have many rows in your table
so first solution is better if it is possible to do.

left() right() and length() functions atleast are able to to do this.
I'm not sure if there is a better way to do it, you can look for such
functions from the manual:

http://dev.mysql.com/doc/mysql/en/String_functions.html

Jul 20 '05 #6
Great point Aggro...
My 2cents...
natural order for me is D01, D02, D03... D12, D13.. etc.

"Aggro" <sp**********@yahoo.com> wrote in message
news:Pe************@read3.inet.fi...
Matias Silva wrote:
I have this list that i'm ordering by units_unit_id and is there way I
can get
a natural order so that D4 and D5 comes before D12


It is not a natural order, natural order is the one you are getting.
Easy way would be to change the table so that D and number would be in
different columns. Another way is to use some string manipulation
functions to parse string into character and a number and then order by
number. This might mean slow queries if you have many rows in your table
so first solution is better if it is possible to do.

left() right() and length() functions atleast are able to to do this.
I'm not sure if there is a better way to do it, you can look for such
functions from the manual:

http://dev.mysql.com/doc/mysql/en/String_functions.html

Jul 20 '05 #7
bobb wrote:
Great point Aggro...
My 2cents...
natural order for me is D01, D02, D03... D12, D13.. etc.


...., D99, D100, etc.
--
Eric Lafontaine
mail http://cerbermail.com/?WaqHmkTe5E
Jul 20 '05 #8
bobb wrote:
Great point Aggro...
My 2cents...
natural order for me is D01, D02, D03... D12, D13.. etc.


...., D99, D100, etc.
--
Eric Lafontaine
mail http://cerbermail.com/?WaqHmkTe5E
Jul 20 '05 #9
bobb wrote:
Great point Aggro...
My 2cents...
natural order for me is D01, D02, D03... D12, D13.. etc.


...., D99, D100, etc.
--
Eric Lafontaine
mail http://cerbermail.com/?WaqHmkTe5E
Jul 20 '05 #10
Yea to me natural order is what bobb has described. I think I'll just
restructure the table so that the table building and unit IDs are separate.

Thanks,
Matt

bobb wrote:
Great point Aggro...
My 2cents...
natural order for me is D01, D02, D03... D12, D13.. etc.

"Aggro" <sp**********@yahoo.com> wrote in message
news:Pe************@read3.inet.fi...
Matias Silva wrote:

I have this list that i'm ordering by units_unit_id and is there way I
can get
a natural order so that D4 and D5 comes before D12


It is not a natural order, natural order is the one you are getting.
Easy way would be to change the table so that D and number would be in
different columns. Another way is to use some string manipulation
functions to parse string into character and a number and then order by
number. This might mean slow queries if you have many rows in your table
so first solution is better if it is possible to do.

left() right() and length() functions atleast are able to to do this.
I'm not sure if there is a better way to do it, you can look for such
functions from the manual:

http://dev.mysql.com/doc/mysql/en/String_functions.html


Jul 20 '05 #11
Yea to me natural order is what bobb has described. I think I'll just
restructure the table so that the table building and unit IDs are separate.

Thanks,
Matt

bobb wrote:
Great point Aggro...
My 2cents...
natural order for me is D01, D02, D03... D12, D13.. etc.

"Aggro" <sp**********@yahoo.com> wrote in message
news:Pe************@read3.inet.fi...
Matias Silva wrote:

I have this list that i'm ordering by units_unit_id and is there way I
can get
a natural order so that D4 and D5 comes before D12


It is not a natural order, natural order is the one you are getting.
Easy way would be to change the table so that D and number would be in
different columns. Another way is to use some string manipulation
functions to parse string into character and a number and then order by
number. This might mean slow queries if you have many rows in your table
so first solution is better if it is possible to do.

left() right() and length() functions atleast are able to to do this.
I'm not sure if there is a better way to do it, you can look for such
functions from the manual:

http://dev.mysql.com/doc/mysql/en/String_functions.html


Jul 20 '05 #12
Yea to me natural order is what bobb has described. I think I'll just
restructure the table so that the table building and unit IDs are separate.

Thanks,
Matt

bobb wrote:
Great point Aggro...
My 2cents...
natural order for me is D01, D02, D03... D12, D13.. etc.

"Aggro" <sp**********@yahoo.com> wrote in message
news:Pe************@read3.inet.fi...
Matias Silva wrote:

I have this list that i'm ordering by units_unit_id and is there way I
can get
a natural order so that D4 and D5 comes before D12


It is not a natural order, natural order is the one you are getting.
Easy way would be to change the table so that D and number would be in
different columns. Another way is to use some string manipulation
functions to parse string into character and a number and then order by
number. This might mean slow queries if you have many rows in your table
so first solution is better if it is possible to do.

left() right() and length() functions atleast are able to to do this.
I'm not sure if there is a better way to do it, you can look for such
functions from the manual:

http://dev.mysql.com/doc/mysql/en/String_functions.html


Jul 20 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Steven T. Hatton | last post: by
27 posts views Thread by Abdullah Kauchali | last post: by
8 posts views Thread by kaosyeti | last post: by
104 posts views Thread by Beowulf | last post: by
54 posts views Thread by Rasjid | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.