Jump to content

Archived

This topic is now archived and is closed to further replies.

Mutley

ORDER by DESC

Recommended Posts

I have a list of numbers and when I order them DESC, it works fine through from 1 to 9 (9 at top, 1 at bottom) but 10, 11, 12 etc is acting like 1 and goes to the bottom to.

How do I correct this?

Share this post


Link to post
Share on other sites
What field types are you using?

Share this post


Link to post
Share on other sites
If you have numbers written in text or characters, and you want to sort them in Ascending order  and you don't want this:
mysql> select number from (table) order by number;

+--------+
| number |
+--------+
| 1      |
| 10    |
| 2      |
| 3      |
| 4      |
| 5      |
| 6      |
| 7      |
| 8      |
| 9      |
+--------+


Use this:
mysql> select number from (table) order by (number+0);

+--------+
| number |
+--------+
| 1      |
| 2      |
| 3      |
| 4      |
| 5      |
| 6      |
| 7      |
| 8      |
| 9      |
| 10    |
+--------+

The (field + 0 ) converts the text/character in the field into an integer.

Alternativley remap your table so that the column containing the numbers is type "integer"

Share this post


Link to post
Share on other sites
Yes, the +0 will cast your character representations as number on-the-fly, but you should definitely change the colun type.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.