I've got a field in a table of type VARCHAR(8). The various values contained in this field include positive and negative numbers, and plain text.
When I run a query, sorting on this field, it doesn't appear to sort correctly. In my ORDER BY I'm type casting this field as follows:
ORDER BY cast(MyField as signed)
Now, if the following values are found in this field all throughout my table:
6, 3, down, slam, stgr, 0, -3
and the query is run, I get something like:
I have no idea why this sorts weirdly. I mean, even multiple instances of the same text values (like "down") aren't even appearing together.
The only thing that I find suspicious is that the Collation for this field (and all fields actually) is latin1_swedish_ci, but when I tried changing it to something like utf8_general_ci, it makes no difference.
To see this live, check out: http://virtuafighter.com/commands/index.php?chara=akira&ver=5b&order=mc&sort=desc
It's basically a command list for a video game. Clicking on the column headings sorts on that column. If you click on the "MC" column, you'll see exactly what I mean with the weird results. Sorting on the "Dmg" column seems fine, however. This field is of the same type, but does not include any text values.
Any clues on how I can sort a field with a mixture of positive and negative numbers, and text, would be appreciated.