CONVERT() not converting properly

I have a table example:

id -- score
1 -- 5.66678
2 -- 11.598
3 -- 12.2
4 -- 11.598
5 -- 2.577777

score is set as a VARCHAR currently.
id is an integer

My simple MySQL query:

SELECT *
FROM `mysql_table`
ORDER BY CONVERT( score, DECIMAL ) DESC

For some reason it pulls it something similar to this:

id -- score
2 -- 11.598
3 -- 12.2
4 -- 11.598
1 -- 5.66678
5 -- 2.577777

12.2 is stuck between the two 11.598's. Is there a reason for this? Am I doing something wrong?

If I were *guessing*, I'd bet that the first 11.598 value there has some extra characters in the VARCHAR field and so the CONVERT() isn't working right on it.

To find out, try something like this:

SELECT id, score, CONVERT(score,DECIMAL) AS numScore
FROM tablename
ORDER BY numscore DESC

See if that makes the problem visible...that is, shows that one or more of the CONVERT's don't work.

If it's only a matter of leading/trailing spaces, just change to

SELECT id, score, CONVERT( TRIM(score), DECIMAL) AS numScore
FROM tablename
ORDER BY numscore DESC

LOL! Ignore my first answer! This one is obvious.


mysql> select * from t;
+------+---------------+
| id | s |
+------+---------------+
| 1 | 11.598 |
| 2 | 11.598 |
+------+---------------+
2 rows in set (0.00 sec)

mysql> select id, s, convert(s,DECIMAL) from t;
+------+---------------+--------------------+
| id | s | convert(s,DECIMAL) |
+------+---------------+--------------------+
| 1 | 11.598 | 12 |
| 2 | 11.598 | 12 |
+------+---------------+--------------------+
2 rows in set (0.00 sec)


See? ROUDING!!!

Now do
mysql> select id, s, convert(s,DECIMAL(20,8)) from t;
+------+---------------+--------------------------+
| id | s | convert(s,DECIMAL(20,8)) |
+------+---------------+--------------------------+
| 1 | 11.598 | 11.59800000 |
| 2 | 11.598 | 11.59800000 |
+------+---------------+--------------------------+
2 rows in set (0.00 sec)

Enough said?