MySQL Order By Problem, Why is 1000 being seen as Smaller than 2?

Question

I have a strange problem,

I am trying to order the output of a set of records by a field called displayOrder. Now even though record A has a displayOrder of 2 and record B has a displayOrder of 1000, record B still shows up before record A. Here's my select statement:

SELECT * FROM items ORDER BY displayOrder ASC

It works fine until I have a record greater than 9, then 10, 11, 12, etc are seen as smaller than 2, 3, 4 because they start with the number 1. Any way to fox this?

Answer 1

Because you choose wrong field type. Integers must be stored in the int type column.

Answer 2

Alphabetically, it is first. A string you compare a letter at a time to figure out which is alpahabetical first (unlike numbers, the one with the most digits isn't always the largest). You will need to use a numerical field type.

Answer 3

It looks like the column type is wrong, as it is sorting alphabetically instead of numerically. If you can't change the column type for some reason, there is a workaround:

SELECT * FROM items ORDER BY displayOrder + 0 ASC