You cant do a natural sort on a string column in MySQL post
CommentsRecently a question popped up over on the CakePHP google group where someone had a problem sorting on a column of "registration numbers". This was his table sorted order by "Reg No ASC":
ID | Name | Reg No |
1 | Product A | 1 |
2 | Product B | 123 |
3 | Product C | 2 |
1, 123, 2. That's not correct right? Well it is if we are sorting strings! The problem here is that MySQL will not do a "natural sort" on a text type column like char, varchar, or text. The solution to this is to change the column type to a numeric value, especially considering he is storing numbers anyway! Once you change "Reg No" to int, the column will sort correctly.
ID | Name | Reg No |
1 | Product A | 1 |
3 | Product C | 2 |
2 | Product B | 123 |
Always remember to check your schema!