How about

SELECT fieldname FROM table ORDER BY CAST(fieldname as SIGNED)

UNSIGNED could replace SIGNED, depending on your needs.

this works for me on Mysql 4.02 alpha on solaris

regards,
sean peters
[EMAIL PROTECTED]


----- Original Message -----
From: "John Almberg" <[EMAIL PROTECTED]>
To: "Mysql" <[EMAIL PROTECTED]>
Sent: Friday, September 27, 2002 8:29 AM
Subject: FW: SQL question


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


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

Reply via email to