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 with the following query:
mysql> select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname,lengt
h(secname)-locate('p',secname))+0), secname);
I was hoping it will order by the number following
each 'swap' in the secname, it doesn't work. It was
ordered instead by 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 |
+----------+------------+
However, if I replace the second expression in the if
statement by date, like the following, it's ordered by
date as I would expect.
mysql> select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',date, secname);
+----------+------------+
| secname | date |
+----------+------------+
| SWAP3 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP5 | 2005-09-21 |
| SWAP1 | 2005-09-21 |
| SWAP10 | 2005-09-21 |
| SWAP2 | 2005-09-21 |
| SWAP0.25 | 2005-09-21 |
| SWAP2 | 2005-09-22 |
| SWAP3 | 2005-09-22 |
| SWAP5 | 2005-09-22 |
| SWAP10 | 2005-09-23 |
| SWAP2 | 2005-09-23 |
| SWAP3 | 2005-09-23 |
| SWAP5 | 2005-09-23 |
| SWAP10 | 2005-09-26 |
| SWAP2 | 2005-09-26 |
| SWAP3 | 2005-09-26 |
| SWAP5 | 2005-09-26 |
+----------+------------+
So I tried different combinations of the second and
third expressions in the if statement in the query,
the next one is the only one I can get it to order my
way, which is not what I wanted of course since I
don't want other secnames than swap% to order this
way.
mysql> select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname, leng
th(secname)-locate('p', secname))+0),
right(secname,length(secname)-locate('p',secname))+0);
+----------+------------+
| secname | date |
+----------+------------+
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1 | 2005-09-21 |
| SWAP2 | 2005-09-22 |
| SWAP2 | 2005-09-26 |
| SWAP2 | 2005-09-21 |
| SWAP2 | 2005-09-23 |
| SWAP3 | 2005-09-22 |
| SWAP3 | 2005-09-26 |
| SWAP3 | 2005-09-21 |
| SWAP3 | 2005-09-23 |
| SWAP5 | 2005-09-23 |
| SWAP5 | 2005-09-22 |
| SWAP5 | 2005-09-26 |
| SWAP5 | 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-21 |
| SWAP10 | 2005-09-23 |
+----------+------------+
Can anyone see what problems I have in my query? I'm
really stuck here. Thanks.
Claire
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
