 Problem On Mysql "order By" |
|
|
|
|
  |
 Jan 15 2005, 04:19 AM
Post
#1
|
|
|
Newbie [Level 3]  Group: Members Posts: 43 Joined: 15-November 04 Member No.: 2,292  |
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 |
 |
 
|
|
Jan 15 2005, 05:29 AM
Post
#2
|
|
|
Administrator Group: Members Posts: 377 Joined: 28-December 04 From: USA Member No.: 2,992 |
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 [WHERE some_condition_is_true] [ORDER BY some_column [ASC | DESC]] [LIMIT offset, rows] So yours should be: CODE 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 |
|
|
|
|
Jan 15 2005, 06:54 AM
Post
#3
|
|
|
Super Member Group: Members Posts: 498 Joined: 23-August 04 Member No.: 878 |
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. |
|
|
|
|
Jan 18 2005, 04:43 PM
Post
#4
|
|
|
Super Member Group: Members Posts: 378 Joined: 8-January 05 Member No.: 3,174 |
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... |
|
|
|
|
Nov 29 2007, 03:00 PM
Post
#5
|
|
|
Trap Double Mocha Member Group: Members Posts: 2,360 Joined: 21-September 07 Member No.: 50,369 |
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 |
|
|
|
|
Dec 6 2007, 05:51 AM
Post
#6
|
|
|
Member [Level 2] Group: Members Posts: 76 Joined: 30-October 05 Member No.: 13,571 |
try set the type of "number" to int
I think the type of your "number" is varchar or something. |
|
|
|
|
Similar Topics
|
Lo-Fi Version | Time is now: 27th July 2008 - 02:21 AM |