Re: strange order by problem

Claire Lee wrote: > 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 l

Re: strange order by problem

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

Re: strange order by problem

[EMAIL PROTECTED] wrote: Claire Lee <[EMAIL PROTECTED]> wrote on 09/27/2005 03:48:11 PM: 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

Re: strange order by problem

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 tha

Re: strange order by problem

Claire Lee <[EMAIL PROTECTED]> wrote on 09/27/2005 03:48:11 PM: > 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. > > So I came up w

RE: strange order by problem

Try this mysql> select distinct secname, date -> from optresult -> where secname like 'swap%' ->and date like '2005-09-2%' -> order by if(secname like 'swap%', -> (mid(secname,5,20)+0), -> secname); +--++ | secname | d