Page 1 of 1

ORDER BY issue

Posted: Wed Feb 13, 2008 10:45 am
by Inkyskin
Hey all,

I order by a text field when pulling out with a certain query. The problem is, a lot of the results start with numbers, and look like this when the query is run:
1 Division
110 Division
12 Division
2 Division
Test Division
Z Division
Is there a way to order it like this:
1 Division
2 Division
12 Division
110 Division
Test Division
Z Division
It's just a standard varchar, but ordering is not the numerical way :( I really don't want to split those with numbers into another column and double sort, that seems messy to me...

Re: ORDER BY issue

Posted: Wed Feb 13, 2008 10:58 am
by Zoxive
Inkyskin wrote: It's just a standard varchar, but ordering is not the numerical way :( I really don't want to split those with numbers into another column and double sort, that seems messy to me...
It is the numerical way, just not the way humans would order it.

Now with your problem it is going to be quite tricky because it has text also.

Try searching google for mysql order by natural, and alike.

I found a new things close to what you have, but not exactly.
http://lists.nyphp.org/pipermail/mysql/ ... 00107.html (Requires constant length)
http://lists.mysql.com/internals/9878 (Making a mysql statment that converts the strings to sortable)
http://blog.feedmarker.com/2006/02/01/h ... -in-mysql/ (+0 converts to numeric)

Re: ORDER BY issue

Posted: Wed Feb 13, 2008 11:11 am
by Inkyskin
Thanks :)

The second link seems to have done the trick - It does put those without any numerics before, but I can live with that. It now sorts like this:
Test Division
Z Division
1 Division
2 Division
12 Division
110 Division