A couple things off the top of my head: - Have you looked into MySQL's 'SET' type?
- What about something like SELECT 0+fieldname as zerofield FROM table ORDER BY zerofield; - Would using grouping help? - Have you considered creating a custom character set? (This may be too extreme. Can be done with SET on the fly?) http://www.mysql.com/doc/en/Character_sets.html Not sure if any of this will work but you can cross them off pretty quick probably. Quoting John Almberg <[EMAIL PROTECTED]>: > > 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
