Casting a character column in mysql for order by
So I'd done this already in postgres, where I needed to sort a character column by integer sort instead of character sort so that the following strings would appear in order:
Using character sort it would be 1,10,11,22,5. Which appears wrong to the naive end user. Therefore, if we know that the column will only have ints we should convert it to an integer column. Sometimes this wont work!
An example: We have a table to store attributes (which are arbitrary). However, we know that attributes of a certain type will always be integers, while the next row might be an attribute from a different type and have string data. So if we only focus on our int values, then we can do the following
ORDER BY CAST(table.row_name AS UNSIGNED)
Now it will sort the above as follows: