Problem On Mysql "order By"

teob

Jan 15 2005, 04:19 AM

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

CODE

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

Comment/Reply (w/o sign-up)

NilsC

Jan 15 2005, 05:29 AM

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

CODE

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:

CODE

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

[br]
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

Comment/Reply (w/o sign-up)

Zenchi

Jan 15 2005, 06:54 AM

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.

Comment/Reply (w/o sign-up)

bjrn

Jan 18 2005, 04:43 PM

QUOTE(Zenchi @ Jan 15 2005, 08:54 AM)

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.

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...

Comment/Reply (w/o sign-up)

FeedBacker

Nov 29 2007, 03:00 PM

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

Comment/Reply (w/o sign-up)

gogoily

Dec 6 2007, 05:51 AM

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

Comment/Reply (w/o sign-up)

(G)IvanW

Aug 1 2009, 06:47 PM

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

Comment/Reply (w/o sign-up)