Problem On Mysql "order By"

[teob 0]

Can someone please help...?
I have a problem with using "ORDER BY" in mysql...

SELECT * FROM `foo` WHERE b='abc' ORDER BY 'number' ASC

i want to sort the table out by the value in "number" which is a number..

but it came out to be sort by like this way...
1->10->2->20
it only sort out the front digit....

but what i want is it will sort by how great the number is like this...
1->2->3........->10....->20

sorry my english isn't good enough...to describe my problem properly...
hope you can understand and help me on this.....thx

[NilsC 0]

I think you have the syntax wrong. Are you creating a query in mySQL? or calling from a php script?

SELECT expressions_and_columns FROM table_name[/br][WHERE some_condition_is_true][br][ORDER BY some_column [ASC | DESC]][/br][LIMIT offset, rows]

So yours should be:

SELECT * FROM foo WHERE b = 'abc' ORDER BY number ASC;

theoretical questions can have only theoretical answers. You would get better help if you use the table names that you have problem with!

Here is a nice link to a tutorial in your problem:
The select statement.

Nils

[Zenchi 0]

This is how mysql sees the way to order those numbers accordingly:2(null)(null), 20(null), 200, 21(null), 210(null)Catch my drift? It's like an ftp directory where you see files listed by numbers, and it looks all messed up. It's not. I believe it has to do with the hexidecimal coding, but again, don't quote me on that.

[bjrn 0]

This is how mysql sees the way to order those numbers accordingly:

 

2(null)(null), 20(null), 200, 21(null), 210(null)

 

Catch my drift? It's like an ftp directory where you see files listed by numbers, and it looks all messed up. It's not. I believe it has to do with the hexidecimal coding, but again, don't quote me on that.

41324[/snapback]

If number is a string field you have a problem. The other thing could be that you should have "ORDER BY number" instead of "ORDER BY 'number'" (without the single quotes). Although that should give an SQL error if it was wrong... Strange...

[iGuest 3]

I had a similar situation with a string field like "SomeString Number", where Number had values like 1, to, 3, ... 55 ...(max to characters) and SomeString had a fixed length = n.I used a update query like this one:<code>update Table set Field = concat(left(Field, n), " 0", right(Field, 1)) where lenth(Field) = n+to;<code/>n+to = n (SomeString) + 1 (space) + 1 (for numbers with 1 digit)So, my Field has values like "SomeString Number", where Number had values like 01, 02, 03, ... 55 ...(exactly to characters) and ORDER BY work well now.I think this is useful for someone , in this particular situation -gims

[gogoily 0]

try set the type of "number" to intI think the type of your "number" is varchar or something.

[iGuest 3]

To order the values as they should be, simply use the SQL ABS function, like so:

SELECT * FROM `foo` WHERE b='abc' ORDER BY ABS(price) ASC

-reply by IvanW