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 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
>
So you want to sort by secname except when secname starts with 'SWAP'
ORDER BY secname
, if (secname like 'swap%'
,(right(secname, length(secname)-locate('p', secname))+0)
,0)
, date;
by giving every *other* entry a default second sort-by of 0, they end up
all sorting according to secname then date. It's when secname starts with
swap that you get the sub-sorting value according to the end of the
string. Make sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
