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
