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(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]
RE: strange order by problem
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 [mailto:[EMAIL PROTECTED] 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(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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strange order by problem
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
Re: strange order by problem
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 - [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]
Re: strange order by problem
[EMAIL PROTECTED] wrote: 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? 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/[EMAIL PROTECTED]
Re: strange order by problem
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]