Re: strange order by problem - Claire Lee - com.mysql.lists.mysql

7 messages in com.mysql.lists.mysqlRe: strange order by problem

From	Sent On	Attachments
Claire Lee	27 Sep 2005 12:47
Gordon Bruce	27 Sep 2005 13:12
SGr...@unimin.com	27 Sep 2005 13:13
Hassan Schroeder	27 Sep 2005 13:43
2wsxdr5	27 Sep 2005 13:43
Claire Lee	27 Sep 2005 14:08
Hassan Schroeder	27 Sep 2005 14:27

Subject:	Re: strange order by problem
From:	Claire Lee (jing...@yahoo.com)
Date:	09/27/2005 02:08:07 PM
List:	com.mysql.lists.mysql

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 <has...@webtuitive.com> 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 ----------------------------- has...@webtuitive.com 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: