This simplified my second expression in the if statement. Thank you. But the query still doesn't sort by the numbers, here's the result:
mysql> select distinct secname, date from optresult where secname like 'swap%' a nd date like '2005-09-2%' order by if (secname like 'swap%',abs(substring(secnam e,5)), secname); +----------+------------+ | secname | date | +----------+------------+ | SWAP0.25 | 2005-09-21 | | SWAP0.5 | 2005-09-21 | | SWAP1 | 2005-09-21 | | SWAP10 | 2005-09-26 | | SWAP10 | 2005-09-23 | | SWAP10 | 2005-09-21 | | SWAP2 | 2005-09-26 | | SWAP2 | 2005-09-23 | | SWAP2 | 2005-09-22 | | SWAP2 | 2005-09-21 | | SWAP3 | 2005-09-21 | | SWAP3 | 2005-09-26 | | SWAP3 | 2005-09-23 | | SWAP3 | 2005-09-22 | | SWAP5 | 2005-09-21 | | SWAP5 | 2005-09-26 | | SWAP5 | 2005-09-23 | | SWAP5 | 2005-09-22 | +----------+------------+ --- Hassan Schroeder <[EMAIL PROTECTED]> wrote: > Claire Lee wrote: > > I need to order a few names by the number > following > > the main name. For example swap2, swap3, swap10 in > the > > order of swap2, swap3, swap10, not in swap10, > swap2, > > swap3 as it will happen when I do an order by. > > ... ORDER BY ABS(SUBSTRING(secname,5)) ... > > will insure that the trailing digits are treated > as numbers :-) > > +----------+------------+ > > | secname | date | > > +----------+------------+ > > | SWAP0.25 | 2005-09-21 | > > | SWAP0.5 | 2005-09-21 | > > | SWAP1 | 2005-09-21 | > > | SWAP10 | 2005-09-26 | > > | SWAP10 | 2005-09-23 | > > | SWAP10 | 2005-09-21 | > > | SWAP2 | 2005-09-26 | > > | SWAP2 | 2005-09-23 | > > | SWAP2 | 2005-09-22 | > > | SWAP2 | 2005-09-21 | > > | SWAP3 | 2005-09-21 | > > | SWAP3 | 2005-09-26 | > > | SWAP3 | 2005-09-23 | > > | SWAP3 | 2005-09-22 | > > | SWAP5 | 2005-09-21 | > > | SWAP5 | 2005-09-26 | > > | SWAP5 | 2005-09-23 | > > | SWAP5 | 2005-09-22 | > > +----------+------------+ > > HTH, > -- > Hassan Schroeder ----------------------------- > [EMAIL PROTECTED] > Webtuitive Design === (+1) 408-938-0567 === > http://webtuitive.com > > dream. code. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
