ORDER BY issue

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

ORDER BY issue

Post 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...
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: ORDER BY issue

Post 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)
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

Re: ORDER BY issue

Post 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
Post Reply