Page 1 of 1

sorting problem

Posted: Fri Apr 04, 2003 3:25 am
by Mobic
if I use:

Code: Select all

$ranktable = "SELECT  number
FROM users ORDER BY number DESC";
I get this result:

12
11
105
10

but I would like it to be:

105
12
11
10

Posted: Fri Apr 04, 2003 3:34 am
by twigletmac
What kind of field do you have that number stored in? VARCHAR or an INT type?

Mac

Posted: Fri Apr 04, 2003 3:38 am
by Mobic
varchar

Posted: Fri Apr 04, 2003 3:48 am
by pootergeist
varchar will sort it as though it is a string - as you have noticed. int will sort it as an integer 2 is lower than 10 sort of thing.

Posted: Fri Apr 04, 2003 3:50 am
by twigletmac
[Edit: Oops too slow - but the links may still be useful]

That's your problem then, the number is being sorted that way because VARCHAR fields are intended for text so whatever's in them will be sorted as a text string. If you want to get the sorting right try a numeric type column like SMALLINT or INT,

More info on INT fields:
http://www.mysql.com/doc/en/Numeric_types.html
http://www.mysql.com/doc/en/Choosing_types.html

Mac

Posted: Fri Apr 04, 2003 5:09 am
by volka
if for some (very wreid) reason you cannot change the field type anymore order by CONVERT(number, unsigned) desc might help. But consider it the last resort.

Posted: Sat Apr 05, 2003 7:20 am
by Mobic
Thanks, it's working now.

One more question;

I want to collect data for a mysql database, add them together and print the result.

Example:

user1 got 23 dollars
user2 got 2 dollars
user3 got 5 dollars
....
user3453 got 6 dollars


what do you do when you should add those numbers, when they are stored in the same field in the database.

Posted: Sat Apr 05, 2003 7:25 am
by volka
change the database structure ;)