Order by field mysql command doesn't work, gives wrong order

Question

Hi. I have a strange problem. I tried this command in mysql:

  SELECT `type`, 
         `bought` 
    FROM cars 
   WHERE owner = 'test' 
ORDER BY type

And it prints out this in phpmyadmin:

type    bought
--------------------
17      1281025497
22      1287708417
22      1287347244
24      1287708324
24      1287876461
6       1287896659
9       1287847238

This doesn't make any sense. 17 is bigger than 22 and 9 less than 6?!

Answer 1

It definitely looks like type column's type is varchar or text (or any string type, really). You have to use an integer type. It is entirely normal that the string "10" is lesser than the string "6" (1 comes before 6).

If you really don't want to change your column's type, you can cast it to an integer.

Answer 2

It's correct if type is a char field.

If they're chars, you're not comparing the numbers six and twenty-four, you're comparing the strings "6" and "24".

"6" is greater than "2", so it comes after.

Answer 3

SELECT `type`, `bought` FROM cars WHERE owner='test' ORDER BY CAST(`type` AS SIGNED)