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 like
> 'swap%',abs(substring(secnam
> e,5)), secname);
SELECT
DISTINCT secname
, date
FROM
optresult
WHERE
secname LIKE 'swap%'
AND
date LIKE '2005-09-2%'
ORDER BY
ABS(SUBSTRING(secname,5))
You don't need the IF in the ORDER BY -- that's already been
constrained by your WHERE ...
> +----------+------------+
> | 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]
