|
Home > Archive > MySQL ODBC Connector > September 2005 > strange order by problem
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
strange order by problem
|
|
| Claire Lee 2005-09-27, 8:24 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(secna
me,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(secna
me, 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/mysql? unsub...sie.nctu.edu.tw
| |
| Gordon Bruce 2005-09-27, 8:24 pm |
| 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 | 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-26 |
| SWAP3 | 2005-09-23 |
| SWAP3 | 2005-09-22 |
| SWAP3 | 2005-09-21 |
| SWAP5 | 2005-09-23 |
| SWAP5 | 2005-09-22 |
| SWAP5 | 2005-09-21 |
| SWAP5 | 2005-09-26 |
+----------+------------+
18 rows in set (0.00 sec)
-----Original Message-----
From: Claire Lee & #91;mailto:jingli71@
yahoo.com]=20
Sent: Tuesday, September 27, 2005 2:48 PM
To: mysql@lists.mysql.com
Subject: strange order by problem
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(secna
me,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(secna
me, 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
=09
____________________
______________=20
Yahoo! Mail - PC Magazine Editors' Choice 2005=20
http://mail.yahoo.com
--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? unsub...
rint.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2005-09-27, 8:24 pm |
| --=_alternative 006ED22685257089_=
Content-Type: text/plain; charset="US-ASCII"
Claire Lee <jingli71@yahoo.com> 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(secna
me,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(secna
me, 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
--=_alternative 006ED22685257089_=--
| |
| Hassan Schroeder 2005-09-27, 8:24 pm |
| 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(secnam
e,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 ----------------------------- hassan@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: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| 2wsxdr5 2005-09-27, 8:24 pm |
| SGreen@unimin.com wrote:
>Claire Lee <jingli71@yahoo.com> wrote on 09/27/2005 03:48:11 PM:
>
>
>
>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?
>
>
If secname is like 'swap%', why are you then using locate to find the p
when it has to be the 4th letter or secname wouldn't be like 'swap%'.
Also if your first order by argument is secname how is the second
argument going to do anything since swap10 and swap2 are different the
first argument is all you need to uniquely identify them.
--
Chris W
Gift Giving Made Easy
Get the gifts you want &
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Claire Lee 2005-09-27, 8:24 pm |
| 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(substrin
g(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 <hassan@webtuitive.com> wrote:
> Claire Lee wrote:
> following
> the
> swap2,
>
> ... ORDER BY ABS(SUBSTRING(secnam
e,5)) ...
>
> will insure that the trailing digits are treated
> as numbers :-)
>
> HTH,
> --
> Hassan Schroeder -----------------------------
> hassan@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:
>
http://lists.mysql.com/mysql? unsub...ahoo
.com
>
>
____________________
____________________
__________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Hassan Schroeder 2005-09-27, 8:24 pm |
| 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(substrin
g(secnam
> e,5)), secname);
SELECT
DISTINCT secname
, date
FROM
optresult
WHERE
secname LIKE 'swap%'
AND
date LIKE '2005-09-2%'
ORDER BY
ABS(SUBSTRING(secnam
e,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 ----------------------------- hassan@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: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|