MySQL ORDER BY numeric value problem with negative numbers

Question

I am using a query which uses:

ORDER BY score DESC;

'score' just holds numeric values, which can also be negative. They don't seem to be displaying in the correct order. The negative numers can appear above positive numbers.

Does anyone know the query I should use to display them like this:

• 10
• 5
• 1
• -1
• -5
• -10

And also to stop them doing this:

* 1
* 10
* 11
* 123
* 1234
* 2
* 25
* 253
* 34

Thanks.

Answer 1

order by cast(score as int) desc;

It appears that you are storing numeric data in a string data type. It would be better to make score a numeric data type, like int.

Answer 2

It's possible to order by a cast varchar by casting the field using:

ORDER BY CAST(`table.field` AS SIGNED) DESC

Where SIGNED will turn the varchar to a integer (which can be a negative value).

A guide is available here: http://www.kreci.net/web-development/sort-varchar-as-int-in-mysql-query/