MySQL ORDER BY

Anon

Hi all,

Does anybody know how to change MySQL's ORDER BY criteria so it orders in natural order, ie:

Instead of string ordering:

1
10
11
2
3
4
5
6
7
8
9

I need:

1
2
3
4
...

The problem is, the field is a string field (CHAR) and it's ordering the results incorrectly.

Any ideas? Thanx a stack!

-- John G

Anon

Well for a field defined as a string it is ordering correctly, I think.

Your field type needs to be set to "integer"

Anon

Thanks -

Okay, stupid question - how would I do that?

I tried looking at http://www.mysql.com, but couldn't find it...

djg_uk

John,

I've had a look at the appropriate docs (http://www.mysql.com/doc/S/E/SELECT.html) and can't find anything which gives an appropriate answer. I suggest using ALTER TABLE to change the field type to NUMBER instead.

Kind Regards,

David

Anon

Thanks David -

Unfortunately, that doesn't seem to be an option in my case - I have to use STRING in that field.

Thanks for the help anyway...

junebug

select id from table order by id asc

[deleted]

What is forcing you to use a string type in the database?
It is extremely bad practice to use strings where numbers are stored.

frog_

can you set the field as an int in the order by statement?

select * from dummy order by int(string);

alastair

Why is it not an option??

'ALTER TABLE tablename CHANGE fieldname fieldname INT' will change the column type to 'Integer'.

This assumes you have ALTER privileges of course.

Also you may get errors if the field currently has a default value that is incompatible with INT or if it has the wrong size set.

Anon

why not dump it all into an array, and then order the array?? just because mysql doesn't support ordering strings containing numbers doesn't mean you can't write your own...

[deleted]

Why not?

because if you want to order in php, you can only order the result that mysql gave you.
That means you can nolonger build queries that depend on ordering, such as

SELECT firstname FROM table ORDER BY score DESC limit 10;

And besides all that, you shouldn't store numbers as strings, ever.

Anon

i wouldn't say EVER... lets say you store building numbers or something... and the possible values are

1,2,3A,3B,4,5,6A

sure, an enum would probably be the choice here, but in other cases this would have to be a string field that sometimes contains "1" or "2"....

Anon

Yeah, it's not something I really have control over. The table is used to store all sorts of data - mostly strings, but in one case numbers.

So is there no way to order results differently when querying MySQL?

steve_m

It is probably too late. I do wish people would not dish out "bad practice" advice until they have been around the block a few times. There are many times when you need to have a field as string rather than integer and then treat it as integer-if-possible. In general this happens when you cannot be sure of your input data but wish to accept it rather than reject. Which is very common in the real world. <sigh!>
The answer to the question is
order by cast( <fieldname> AS UNSIGNED )

I can't speak for efficiency. If speed is the answer and you have a large set then you need to take a different tack.

johanafm

Anon;616466 wrote:
why not dump it all into an array, and then order the array?? just because mysql doesn't support ordering strings containing numbers doesn't mean you can't write your own...

Once again, as was pointed out in one of the first posts, MySQL does handle ordering perfectly.

1
12
2

is correct if they are strings, and corresponds to the sequence in which you wrote things like

3
3B
4

or why not just plain strings

A
Ab
B

If you expect 1, 2, 12, then you should also get A, B, Ab...

wookie

Anon;616353 wrote:
Hi all,

Does anybody know how to change MySQL's ORDER BY criteria so it orders in natural order, ie:

Instead of string ordering:

1
10
11
2
3
4
5
6
7
8
9

I need:

1
2
3
4
...

The problem is, the field is a string field (CHAR) and it's ordering the results incorrectly.

Any ideas? Thanx a stack!

-- John G

I know this is very old, but I stumbled upon it and having found no useful answers here I worked out a fairly simply solution. Maybe it will help others.

SELECT text_int
FROM table
ORDER BY LENGTH(text_int), text_int;

This is not a perfect solution e.g. If your text numbers are padded with an arbitary number of zeros then they will get put in the wrong place, but for the simple example described here this will do ... and doesn't require us rebuiding the DB from the ground up (which is the MySQL equivalent of the IT guy asking if you can reinstall Windows or recompile your Linux Kernel)

Wookie

Ashley_Sheridan

You say the table is used to store all sorts of data, but is that specific field used to store all sorts of specific data, as none of the example entries for that field contain anything other than numerical characters.

Roger_Ramjet

And the 'real' answer to this is to add a new Collation specific to the needs of the case. An example in the manual that seems to fit this case is the sorting of Phone Numbers with formatting and punctuation.

Just to add my 2 cents to this resurrected thread 🙂