Jump to content


Photo

ORDER by DESC


  • Please log in to reply
4 replies to this topic

#1 Mutley

Mutley
  • Members
  • PipPipPip
  • Advanced Member
  • 765 posts

Posted 08 October 2006 - 11:28 AM

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?
~ Mutley.

#2 john_6767

john_6767
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts

Posted 08 October 2006 - 11:39 AM

u can put 0 in front of the numbers smaller than 10..

#3 trq

trq
  • Staff Alumni
  • Advanced Member
  • 31,041 posts

Posted 08 October 2006 - 11:40 AM

What field types are you using?

#4 paul2463

paul2463
  • Members
  • PipPipPip
  • Advanced Member
  • 955 posts
  • LocationUK

Posted 08 October 2006 - 11:42 AM

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"
you cannot affect the past but you can ruin a perfectly good present by worrying about the future

*********************************************************************
The <A HREF="http://www.php.net/d...php">MANUAL</A> is actually a useful resource

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 October 2006 - 05:51 AM

Yes, the +0 will cast your character representations as number on-the-fly, but you should definitely change the colun type.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users