ORDER BY doesn't like numbers.

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
Gen-ik
DevNet Resident
Posts: 1059
Joined: Mon Aug 12, 2002 7:08 pm
Location: London. UK.

ORDER BY doesn't like numbers.

Post by Gen-ik »

I have a MySQL database table with one of the colums names ID.

I do a mysql_query and ORDER BY id.

I'm not getting the expected result as mysql seems to be treating the numbers as strings.

I get something like this..

1, 10, 11, 2, 3, 31, 33.. and so on.

Is there any thing I can do about this?


Thanks for your time.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

if id is kind of a numerical value (tiny/medium/large int, ....) mysql uses the correct natural order.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Yup, if you're storing your number in a varchar column (ie. a text column intended for strings) instead of one intended for integers then MySQL will treat the numbers as strings. If however, you are storing the numbers in a tiny/small/medium/large int column then you've got another prob somewhere.

Mac
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post by Rob the R »

I'm sure Volka and Mac are right, but if the column type has to remain a varchar, you can try using the CAST function in the ORDER BY clause:
SELECT id FROM table ORDER BY CAST(id as SIGNED)

I don't have MySQL in front of me, so I can't confirm if this will work or not, but I can't find any evidence it wouldn't :wink:
Post Reply