Here is a small example I have just run :
mysql> create table test (f char(20));
Query OK, 0 rows affected (0.90 sec)
mysql> insert into test values ('11xbc');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values ('2bcv');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values ('1abc');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test order by 0+f;
+-------+
| f |
+-------+
| 1abc |
| 2bcv |
| 11xbc |
+-------+
3 rows in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 3.23.39 |
+-----------+
1 row in set (0.64 sec)
Either you have a syntax error in your command or your version of mysql
is too old. May be you should consider an upgrade to 3.23.
Hope this helps
--
Joseph Bueno
John Almberg wrote:
> Nope. I've tried every combination I can think of of these ideas. They all
> give syntax errors. I don't think arithmatic is allowed in an ORDER BY
> clause. Doesn't even work on an INT field.
>
> -- John
>
>
>>-----Original Message-----
>>From: gerald_clark [mailto:[EMAIL PROTECTED]]
>>Sent: Friday, September 27, 2002 9:52 AM
>>To: John Almberg
>>Cc: Mysql
>>Subject: Re: SQL question
>>
>>
>>ORDER BY 0 + fieldname
>>
>>if this is not quit right try
>>
>>ORDER BY 0 + fieldname , fieldname
>>
>>John Almberg wrote:
>>
>>
>>>That gives a syntax error, unfortunately.
>>>
>>>-- JOhn
>>>
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>From: Mihail Manolov [mailto:[EMAIL PROTECTED]]
>>>>Sent: Thursday, September 26, 2002 9:51 PM
>>>>To: Mysql
>>>>Subject: Re: SQL question
>>>>
>>>>
>>>>Try:
>>>>
>>>>SELECT fieldname FROM table ORDER 0+fieldname;
>>>>
>>>>
>>>>Hope it helps.
>>>>
>>>>
>>>>Mihail
>>>>
>>>>----- Original Message -----
>>>>From: "John Almberg" <[EMAIL PROTECTED]>
>>>>To: "Mysql" <[EMAIL PROTECTED]>
>>>>Sent: Thursday, September 26, 2002 8:04 PM
>>>>Subject: SQL question
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>I'm trying to sort a table on a character-type field that
>>>>>
>>>>>
>>>>
>>>>contains mostly
>>>>
>>>>
>>>>
>>>>>numbers. This field always contains either a number or a
>>>>
>>number followed
>>
>>>>>
>>>>by
>>>>
>>>>
>>>>
>>>>>a character. Like '57' or '57a'.
>>>>>
>>>>>I'd like to sort the table *numerically* on this field, not
>>>>>
>>>>>
>>>>
>>>>*alphabetically*
>>>>
>>>>
>>>>
>>>>>on this field. That is, I'd like the table to be sorted like:
>>>>>
>>>>>1 ...
>>>>>2 ...
>>>>>2a ...
>>>>>3 ...
>>>>>4d ...
>>>>>
>>>>>NOT like:
>>>>>
>>>>>1 ...
>>>>>11 ...
>>>>>111a ...
>>>>>2a ...
>>>>>22 ...
>>>>>
>>>>>See what I mean? This is a common problem, I think, when you sort an
>>>>>character type field that contains numbers. The sort comes out
>>>>>
>>>>>
>>>>
>>>>all wrong.
>>>>
>>>>
>>>>
>>>>>Is there anyway I can achieve this sort using SQL? The target server is
>>>>>running 3.22.32. Any ideas greatly appreciated!
>>>>>
>>>>>-- John
>>>>>
>>>>>
>>>>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
