racking my brain sorting greatest to least with SQL

[mw-dnb]

ok guys - i've tried a couple things and I'm still having a strange occurence.

I'll go ahead and fill you in on what exactly i need to do

I have a column (text) that I need to sort

The colums name is points, its stores points for users profiles.

+-------+
|points|
+-------+
| 23|
| 5323|
| 164|
| 8|
+-------+

I'm using the following SQL query:

$result = mysql_query("SELECT id, profile_type, artist, city, state, points, image, bio, views FROM artist_profiles ORDER BY points, views DESC",$db);

It returns it sorted properly....however - not by value. Meaning

It would list:
+-------+
|points|
+-------+
| 164|
| 23|
| 5323|
| 8|
+-------+

When I need it to sort like this:
+-------+
|points|
+-------+
| 5323|
| 164|
| 23|
| 8|
+-------+

This way I can list there points from greatest to least. The only way I have thought I could get around it is by making the values a fixed length, say 12 charecters. However this will definetly bloat the database:

Making the data something similar too:

+-------+
|points|
+-------+
|05323|
|00164|
|00023|
|00008|
+-------+

Which would be a very large inconvience. If anyone has any help or suggestions it is greatly appreciated. Normally I wouldn't seek out this much help, but this has taken me the last 3 nights to sort out and I haven't come to a good conclusion yet.

Thanks ahead of time

😃

[Weedpacket]

Try using a numeric type for the table column, instead of a text type. Then the database will sort it numerically instead of lexicographically.

[bfinucan]

Hello,
The post by Weedpacket is all you need to know to fix the problem.

You'll notice when you look at your values that the very first characters are in fact, sorted in ascending order (1-9). This is because you are using a "text" field instead of numeric. Your values are being sorted alphabetically (0-9, A-Z), not numerically. Change the field to an integer type and your problems will be gone.

-Brandon

[mw-dnb]

i changed it to INT and MEDIUMINT and still no luck. I had originally use a Integer for the values but it still sorted alphanumericly rather then by value.

I'm going to take a couple more trys at it and see if I can get down to the problem.