Okay, this may be a little off topic.
In my mysql database, I have a text field that contains almost entirely numbers. I need to sort by that field, but I need it to sort like this:
33
35
401
1005
2234
Currently, it's sorting like this:
1005
2234
33
35
401
In my sql query, how do I convert this field to numbers in the query so that I can sort it correctly?
Thanks.
You can either A: Change your db schema to it's stored as a number of some kind (int, numeric etc...) or you can B: Cast it on output. I don't think mysql v 3.xx.yy support casting, but the new ones do and most other db engines have for some time now:
select cast(textfield as int) as nowanum from table order by nowanum;
Thanks for the reply. I actually just figured it out for myself. If you add +0 to the ORDER BY field, it sorts it on its numeric value. So, for example, I'm sorting by booth number, which is text because there are booths with numbers like 1075A, and others where the booth number is 356-357.
I have discovered that this:
ORDER BY boothNumber+0
sorts the field on it's numeric value.
nice hack! I'll have to remember that next time I'm using mysql 3...