Sorting Rows (ORDER BY)

[Sylvester21]

Hey guys,

When I display results from the database on my webpage, I want to order them from most to least, like this:

1
2
3
4
5
6

But, now it comes out something like this:

199
1722
177
3133
342

While it should be like:

3133
1722
342
199
177

How would I do this?

[mlseim]

Are you talking about querying a MySQL database?
Or is this some sort of array sort from a text file database?

I'm thinking in the lines of you're sorting as an alpha string, not an integer.
But not sure what your scripting looks like right now.

[masterofollies]

Did you try this as a test?

ORDER BY number DESC LIMIT 10

[Fou-Lu]

Are you talking about querying a MySQL database?
Or is this some sort of array sort from a text file database?

I'm thinking in the lines of you're sorting as an alpha string, not an integer.
But not sure what your scripting looks like right now.

I agree, it appears to be nat sorted radix. The only thing I can think of that does this by itself is string sorts.
In any case, it is likely a SQL problem (I'm only basing that off of the title ORDER BY), so I'll move it into the mysql forum for now.

[Len Whistler]

I had a similar problem with ORDER BY tablename, I think the default is by date if you don't specify DESC or ASC as in masterofollies post #3.


199
1722
177
3133
342


My guess is 199 was entered first, 342 was last.

[Sylvester21]

Well I have a field in the table called `score`, where a certain score of people is being displayed. I am doing ORDER BY `score` DESC, and when I ORDER BY `score` ASC, it's also the same problem. It sorts by the first integer, not the whole result, like this:

1
100
11
102
12

And so on. While it should be:

1
11
12
100
102

[Len Whistler]

What is the data type for the column?

Try VARCHAR or INT UNSIGNED ZEROFILL. The zerofill will give you:


001
100
011
102
012

[Old Pedant]

I think the default is by date if you don't specify
The default is by primary key.

****************

Obviously Sylvester has goofed and stored Score as some kind of string type. Probably VARCHAR().

The best answer is to change the datatype in the database to INT or some other numeric type.

The second best answer is to CAST it to an integer:

...
ORDER BY CAST( Score AS INT ) ASC


The ASC is optional, since it's the default.

http://dev.mysql.com/doc/refman/5.1/en/cast-functions.html#function_cast

[Sylvester21]

Yup, was indeed VARCHAR instead of INT, so just by changing that directly into the database solved my problem. Thanks a lot for the helpfull replies.