my boss figured out a nice solution to this
ORDER BY IF(ABS(Item) > 0,LPAD(Item,9,'0'),Item) This will pad numbers ( ok it might not do so hot with DECIMAL, but it can be fixed to work I supposed) with 0 So it should sort 000000001 000000002 000000010 000000022 000000050 000000230 instead of 1 10 2 22 230 50 And it will skip padding text cause I guess the ABS of text is 0, and the "numbers" and words will appear separated from each other My boss is smart :) -----Original Message----- From: mike [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 09, 2002 10:11 AM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: ORDER BY >From my limited experience. I believe your gonna have to split that one field of alpha and numeric into two fields one for the alpha info and one for the numeric info. Then you can sort the information the way you want. Mike ----- Original Message ----- From: "Luc Foisy" <[EMAIL PROTECTED]> To: "MYSQL-List (E-mail)" <[EMAIL PROTECTED]> Sent: Tuesday, April 09, 2002 9:55 AM Subject: ORDER BY > > If we have char data field > mixed alpha + numeric data is entered, alpha data is entered, and numeric > data is entered > when we sort this, we would like the numeric data sorted numerically instead > of alphabetically ( while the rest of course sorts alphabetically ) > do I have some kind of option here? > > Luc Foisy > sql,query > > > --------------------------------------------------------------------- > 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.342 / Virus Database: 189 - Release Date: 3/14/02 --------------------------------------------------------------------- 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
