 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / October 2005Order By values of a column
Hi, I want the result of my query to be returned in a specific order based upon values of a column. It will be more clear with example. Let's say my table is as below Col1 Col2 Col3 1 A Mark 2 A Rob 3 C Chris 4 B Tom 5 C Harry I want the result to be returned sorted on Col2 in the order of values B, C and A. So, I want to get it sorted as: Col1 Col2 Col3 4 B Tom 3 C Chris 5 C Harry 1 A Mark 2 A Rob Is this possible? How? Thanks in advance, Lalit > Is this possible? How? select * from .... where .... order by col2 ASC (or DESC for descending order) Bernd  Signature"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?" "Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe des 18. März in Berlin > Hi, > [quoted text clipped - 11 lines] > I want the result to be returned sorted on Col2 in the order of values > B, C and A. So, I want to get it sorted as: select ... order by case col2 when 'B' then 1 when 'C' then 2 when 'A' then 3 else 4 end > Hi, > [quoted text clipped - 23 lines] > Thanks in advance, > Lalit order by translate(col2,'312','ABC'), col1 Thanks for your replies. Below are my problems: While running with CASE logic written by IAN, I am getting SQLCODE -214. For Bob's translate function. it will work if I really had col2 with length as one character. But I actually have values in col2 like 'EB', 'TVD', DIFFDE' etc. Bob, any clues on that? > Thanks for your replies. Below are my problems: > [quoted text clipped - 4 lines] > length as one character. But I actually have values in col2 like 'EB', > 'TVD', DIFFDE' etc. Bob, any clues on that? Which DB2 version on which OS are you using? DB2 version 7 Sorry for the incomplete info, I am using DB2 versions 7 on OS390. > Thanks for your replies. Below are my problems: > [quoted text clipped - 4 lines] > length as one character. But I actually have values in col2 like 'EB', > 'TVD', DIFFDE' etc. Bob, any clues on that? First thing popping into my head was to create a table which mapped col2 to numerical weights, joining with that, and ordering based on the weights. Allows you to separate out the data (col2 <==> order/weight) from the logic, meaning that if you want to tweak the order, or add new types, you can do it outside of your code. ORDER BY POSSTR('EB ' || 'TVD ' || 'DIFFDE' || 'Bob ', col2) / 6 sweet and smart, nice Tomkuma None of the functions like POSSTR, TRANSLATE, LOCATE or CASE statement seems to be working with V7 POSSTR is definitely available in V7 zOS. what error message do you get ? Hope you are still following: POSSTR() is not accepting a column name on the second position. Use LOCATE instead, that does accept columns on both positions. Be aware that the order of the arguments is the other way round, so the statement should be: ORDER BY LOCATE(cols, 'EB ' || 'TVD ' || 'DIFFDE' || 'Bob ') / 6 Promise that does the job. SELECT DEPTNO, LOCATE(DEPTNO,'B01' || 'A00' || 'C01' || 'D01' || 'D11' || 'D21' || 'E01' || 'E11' || 'E21') FROM XXXXXXXX.DEPT ORDER BY 2 returns: DEPTNO COL1 ------ ----------- B01 1 A00 4 C01 7 D01 10 D11 13 D21 16 E01 19 E11 22 E21 25 Hope you are still following: POSSTR() is not accepting a column name on the second position. Use LOCATE instead, that does accept columns on both positions. Be aware that the order of the arguments is the other way round, so the statement should be: ORDER BY LOCATE(cols, 'EB ' || 'TVD ' || 'DIFFDE' || 'Bob ') / 6 Promise that does the job. SELECT DEPTNO, LOCATE(DEPTNO,'B01' || 'A00' || 'C01' || 'D01' || 'D11' || 'D21' || 'E01' || 'E11' || 'E21') FROM XXXXXXXX.DEPT ORDER BY 2 returns: DEPTNO COL1 ------ ----------- B01 1 A00 4 C01 7 D01 10 D11 13 D21 16 E01 19 E11 22 E21 25 singlal wrote "I am using DB2 versions 7 on OS390." It is written in DB2 UDB SQL Reference Version 7 that a column name can be used as search-string for POSSTR. But, I failed in another query to use a column as search-string. So, you are right. Perhaps manual's bug. Well, for z/OS there is an own SQL reference, which states it is not possible. I got confused as well, and I was surprised it works with locate :-) See V8 zOS docs: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.sql ref/bjnrmstr280.htm I saw your referenced doc and PDF version of SQL Reference Version 8. Column name was removed from search-string for POSSTR. I guess manual bug of Version 7 was corrected. For LOCATE, column name is included usable expression for search-string in both docs. So, your result is consist with Version 8 manual. If none of the other solutions work well: SELECT Col1, Col2, Col3, 1 Sort_Order FROM Table WHERE Col2 = 'B' UNION ALL SELECT Col1, Col2, Col3, 2 Sort_Order FROM Table WHERE Col2 = 'C' UNION ALL SELECT Col1, Col2, Col3, 3 Sort_Order FROM Table WHERE Col2 = 'A' ORDER BY 4 B. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.