MySQL ORDER BY numerical problem with negative numbers

Question

I am using a query that uses:

ORDER BY score DESC;

'score' simply contains numerical values, which can also be negative. They do not seem to appear in the correct order. Negative numbers may appear above positive numbers.

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

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

And also to stop them:

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

Thank.

Answer 1

order by cast(score as int) desc;

It looks like you are storing numerical data in a string data type. It would be better to make a numeric data type, for example int.

Answer 2

You can execute the order with varchar by saying a field using:

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

SIGNED varchar ( ).

: http://www.kreci.net/web-development/sort-varchar-as-int-in-mysql-query/

Answer 3

Your data type for evaluation is probably a char type. By correctly specifying your data type as a signed integer or decimal with the correct precision, this problem will disappear.

Answer 4

I have no problem getting negative numbers to be ordered from highest to lowest ... Here is my code and CREATE query for your reference:

CREATE TABLE `test` (
  `score` INT(10) NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

select * from test order by score desc;

And a dump from my database:

INSERT INTO `test` (`score`) VALUES (10);
INSERT INTO `test` (`score`) VALUES (5);
INSERT INTO `test` (`score`) VALUES (1);
INSERT INTO `test` (`score`) VALUES (-1);
INSERT INTO `test` (`score`) VALUES (-5);
INSERT INTO `test` (`score`) VALUES (-10);
INSERT INTO `test` (`score`) VALUES (-11);
INSERT INTO `test` (`score`) VALUES (7);

Hope this helps ...