Re: SQL Brain Teaser Challenge
Note 1009345.6 on MetaLink provides a solution similar to the one proposed with an index. Jared On Tuesday 05 November 2002 09:24, Orr, Steve wrote: Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Brain Teaser Challenge
based on the date it was written, that was before hints were available for use. so it makes sense to include the dummy comparison to the indexed column in the where clause as described in the note. You mean Oracle sometimes thinks of these things BEFORE we do? nah! --- Jared Still [EMAIL PROTECTED] wrote: Note 1009345.6 on MetaLink provides a solution similar to the one proposed with an index. Jared On Tuesday 05 November 2002 09:24, Orr, Steve wrote: Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Thanks Kevin, good to hear from you. As usual you're Johnie on spot with TFM. It's interesting that this can be overcome with the inline view technique posted earlier by Raj. Steve -Original Message- Sent: Wednesday, November 06, 2002 5:23 AM To: Multiple recipients of list ORACLE-L Directly from TFM Notes on Hierarchical Queries: If you specify a hierarchical query and also specify the ORDER BY clause, the ORDER BY clause takes precedence over any ordering specified by the hierarchical query, unless you specify the SIBLINGS keyword in the ORDER BY clause. The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join: * If the WHERE predicate contains a join, Oracle applies the join predicates before doing the CONNECT BY processing. * Oracle applies any non-join predicates (that is, all predicates if the WHERE clause does not contain a join) after doing the CONNECT BY processing without affecting the other rows of the hierarchy. -Original Message- Sent: Tuesday, November 05, 2002 3:29 PM To: Multiple recipients of list ORACLE-L I was also able to confirm this works on O9i. -Original Message- Sent: Tuesday, November 05, 2002 11:14 AM To: Multiple recipients of list ORACLE-L I get an error on 8.1.7.2. Is siblings new? SQL l 1 SELECT LEVEL, treenode.* 2FROM treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* ORDER SIBLINGS BY PARENTid , nodeorder SQL / ORDER SIBLINGS BY PARENTid , nodeorder * ERROR at line 5: ORA-00924: missing BY keyword -Original Message- Sent: Tuesday, November 05, 2002 11:02 AM To: '[EMAIL PROTECTED]'; Orr, Steve SELECT LEVEL, treenode.* FROM treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid ORDER SIBLINGS BY PARENTid , nodeorder Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: SQL Brain Teaser Challenge
Hey Jared, just got this because I was on a 4X10 day off yesterday. Anyway, thanks for the info. There was lots of great discussion on this and I appreciate the collective brain power of the list. Proposed solutions: 1) upgrade to Oracle 9i and use an inline view; 2) use a hint; 3) use indexes on the columns to be sorted; 4) use an undocumented parameter; 5) Metalink... No help yet. Steve Orr Bozeman, Montana -Original Message- Sent: Wednesday, November 06, 2002 12:31 PM To: Multiple recipients of list ORACLE-L Importance: High I'm seeing some very strange results using _new_connect_by_enabled = true Login to the database, run these 2 commands: alter session set _new_connect_by_enabled = true; 1 SELECT * 2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* order by parentid, nodeorder 11:22:48 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. 11:22:49 rsysdevdb.radisys.com - jkstill@dv01 SQL These are the expected results. Now I comment out the 'FROM' clause, and add a new FROM and ORDER by: SELECT * -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) from treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid order by parentid, nodeorder; Here are the results: SELECT * 2 -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 from treenode 4 START WITH parentid=0 5 CONNECT BY PRIOR ID = parentid 6* order by parentid, nodeorder 11:24:21 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. 11:24:22 rsysdevdb.radisys.com - jkstill@dv01 SQL Hey look! The output is working just the way we would like, without the inline view. Now, remove the commented out line: SELECT * from treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid order by parentid, nodeorder; And the resulting output: 1 SELECT * 2 from treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* order by parentid, nodeorder 11:25:12 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 7 1 2 3rd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 10 3 1 nested folder2.2.1 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 10 2 nested folder2.2.2 11 rows selected. 11:25:13 rsysdevdb.radisys.com - jkstill@dv01 SQL Interesting, no? Jared Madhavan Amruthur [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 08:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: SQL Brain Teaser Challenge Hi Steve, You can get the below query to work in 8.1.7 (not sure about previous versions) by setting the undoc parameter _new_connect_by_enabled = true and can be set for a session SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid You can also use sys_connect_by_path feature in 9i which gives you the entire hierarchy path (can be used in 8i with the above undoc parameter) For eg: select sys_connect_by_path(parent_id,'/') from treenode start with parent_id = 0 connect by prior id = parent_id will give you a output like 0/1/9 0/1/2 0/1/2/4 ...etc As always setting
RE: SQL Brain Teaser Challenge
To add another point, somebody else (the thread is too long for this lazy git to go back and search who! ;P) mentioned that the access plans *could* change if #3 were the solution you go for - however this can be overcome with the use of stored outlines.. my 2 pence :) Mark -Original Message- Sent: 07 November 2002 15:49 To: Multiple recipients of list ORACLE-L Hey Jared, just got this because I was on a 4X10 day off yesterday. Anyway, thanks for the info. There was lots of great discussion on this and I appreciate the collective brain power of the list. Proposed solutions: 1) upgrade to Oracle 9i and use an inline view; 2) use a hint; 3) use indexes on the columns to be sorted; 4) use an undocumented parameter; 5) Metalink... No help yet. Steve Orr Bozeman, Montana -Original Message- Sent: Wednesday, November 06, 2002 12:31 PM To: Multiple recipients of list ORACLE-L Importance: High I'm seeing some very strange results using _new_connect_by_enabled = true Login to the database, run these 2 commands: alter session set _new_connect_by_enabled = true; 1 SELECT * 2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* order by parentid, nodeorder 11:22:48 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. 11:22:49 rsysdevdb.radisys.com - jkstill@dv01 SQL These are the expected results. Now I comment out the 'FROM' clause, and add a new FROM and ORDER by: SELECT * -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) from treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid order by parentid, nodeorder; Here are the results: SELECT * 2 -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 from treenode 4 START WITH parentid=0 5 CONNECT BY PRIOR ID = parentid 6* order by parentid, nodeorder 11:24:21 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. 11:24:22 rsysdevdb.radisys.com - jkstill@dv01 SQL Hey look! The output is working just the way we would like, without the inline view. Now, remove the commented out line: SELECT * from treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid order by parentid, nodeorder; And the resulting output: 1 SELECT * 2 from treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* order by parentid, nodeorder 11:25:12 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 7 1 2 3rd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 10 3 1 nested folder2.2.1 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 10 2 nested folder2.2.2 11 rows selected. 11:25:13 rsysdevdb.radisys.com - jkstill@dv01 SQL Interesting, no? Jared Madhavan Amruthur [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 08:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: SQL Brain Teaser Challenge Hi Steve, You can get the below query to work in 8.1.7 (not sure about previous versions) by setting the undoc parameter _new_connect_by_enabled = true and can be set for a session SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT
RE: SQL Brain Teaser Challenge
Hi Steve/Jared, It was coincidence that just when Steve posted this we were trying to flatten the hierarchy and PL/SQL was the only option and I was doing some reserach and stumbled upon this parameter and then I used to it to generate output using the sys_connect_by_path and then all I had to do was parse the output and then populate the level columns based on that to get my complete hierarchy You are right Jared, once you set the parameter, most of the things (except the siblings as its a new keyword only since 9i I think) start working as in 9i but with a undoc parameter. Regards, Madhavan http://www.dpapps.com On Thu, 07 Nov 2002 07:49:22 -0800, Orr, Steve [EMAIL PROTECTED] said: Hey Jared, just got this because I was on a 4X10 day off yesterday. Anyway, thanks for the info. There was lots of great discussion on this and I appreciate the collective brain power of the list. Proposed solutions: 1) upgrade to Oracle 9i and use an inline view; 2) use a hint; 3) use indexes on the columns to be sorted; 4) use an undocumented parameter; 5) Metalink... No help yet. Steve Orr Bozeman, Montana -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm - The way an email service should be -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
And surprise, surprise... per Metalink, OWS is unwilling to support the undocumented parameter approach. -Original Message- Sent: Thursday, November 07, 2002 9:14 AM To: Multiple recipients of list ORACLE-L To add another point, somebody else (the thread is too long for this lazy git to go back and search who! ;P) mentioned that the access plans *could* change if #3 were the solution you go for - however this can be overcome with the use of stored outlines.. my 2 pence :) Mark -Original Message- Sent: 07 November 2002 15:49 To: Multiple recipients of list ORACLE-L Hey Jared, just got this because I was on a 4X10 day off yesterday. Anyway, thanks for the info. There was lots of great discussion on this and I appreciate the collective brain power of the list. Proposed solutions: 1) upgrade to Oracle 9i and use an inline view; 2) use a hint; 3) use indexes on the columns to be sorted; 4) use an undocumented parameter; 5) Metalink... No help yet. Steve Orr Bozeman, Montana -Original Message- Sent: Wednesday, November 06, 2002 12:31 PM To: Multiple recipients of list ORACLE-L Importance: High I'm seeing some very strange results using _new_connect_by_enabled = true Login to the database, run these 2 commands: alter session set _new_connect_by_enabled = true; 1 SELECT * 2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* order by parentid, nodeorder 11:22:48 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. 11:22:49 rsysdevdb.radisys.com - jkstill@dv01 SQL These are the expected results. Now I comment out the 'FROM' clause, and add a new FROM and ORDER by: SELECT * -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) from treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid order by parentid, nodeorder; Here are the results: SELECT * 2 -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 from treenode 4 START WITH parentid=0 5 CONNECT BY PRIOR ID = parentid 6* order by parentid, nodeorder 11:24:21 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. 11:24:22 rsysdevdb.radisys.com - jkstill@dv01 SQL Hey look! The output is working just the way we would like, without the inline view. Now, remove the commented out line: SELECT * from treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid order by parentid, nodeorder; And the resulting output: 1 SELECT * 2 from treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* order by parentid, nodeorder 11:25:12 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 7 1 2 3rd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 10 3 1 nested folder2.2.1 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 10 2 nested folder2.2.2 11 rows selected. 11:25:13 rsysdevdb.radisys.com - jkstill@dv01 SQL Interesting, no? Jared Madhavan Amruthur [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 08:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: SQL Brain Teaser Challenge Hi Steve, You can get the below query to work in 8.1.7 (not sure about
RE: RE: SQL Brain Teaser Challenge
Ooops. Was rather late (or rather early) when I sent this. Obviously the ascending CONNECT BY subquery should not be in the WHERE clause but in the descending CONNECT BY to stop recursion. - Original Message - From: Orr, Steve [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 05 Nov 2002 16:43:26 I have a solution which doesn't rely on hints, but I am not very satisfied with it either. Innovative nonetheless. Another cool way to skin this cat. Thanks! Steve -Original Message- Sent: Tuesday, November 05, 2002 5:14 PM To: Multiple recipients of list ORACLE-L Importance: High Orr, Steve wrote: What do I win? This was stated in the very first post... kudos. :-) At the moment you and Rich Jesse are tied but I'm still not very pleased with the solution. But unless somebody comes up with something better I'll box you up some kudos for shipping. (I afraid to ask but what are kudos anyway?) Thanks. Tentatively yours, Steve Steve, I have a solution which doesn't rely on hints, but I am not very satisfied with it either. It relies on a function, and performance will be likely to be dismal if your tree grows big. Here is the function : create or replace function tree_rank(p_id in number) return number is n_rank number; begin select sum(nodeorder * power(10, -1 * level)) into n_rank from treenode where id in (select id from treenode connect by id = prior parentid start with id = p_id) connect by parentid = prior id start with id = 1; return n_rank; end; / (double 'CONNECT BY', ouch). Note that if you expect more than 10 items per level, you should use somthing bigger than 10 in the power function. However : SQL select * from treenode 2 order by tree_rank(id); ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --- -- --- -- Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Directly from TFM Notes on Hierarchical Queries: If you specify a hierarchical query and also specify the ORDER BY clause, the ORDER BY clause takes precedence over any ordering specified by the hierarchical query, unless you specify the SIBLINGS keyword in the ORDER BY clause. The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join: * If the WHERE predicate contains a join, Oracle applies the join predicates before doing the CONNECT BY processing. * Oracle applies any non-join predicates (that is, all predicates if the WHERE clause does not contain a join) after doing the CONNECT BY processing without affecting the other rows of the hierarchy. -Original Message- Sent: Tuesday, November 05, 2002 3:29 PM To: Multiple recipients of list ORACLE-L I was also able to confirm this works on O9i. -Original Message- Sent: Tuesday, November 05, 2002 11:14 AM To: Multiple recipients of list ORACLE-L I get an error on 8.1.7.2. Is siblings new? SQL l 1 SELECT LEVEL, treenode.* 2FROM treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* ORDER SIBLINGS BY PARENTid , nodeorder SQL / ORDER SIBLINGS BY PARENTid , nodeorder * ERROR at line 5: ORA-00924: missing BY keyword -Original Message- Sent: Tuesday, November 05, 2002 11:02 AM To: '[EMAIL PROTECTED]'; Orr, Steve SELECT LEVEL, treenode.* FROM treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid ORDER SIBLINGS BY PARENTid , nodeorder Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may
RE: SQL Brain Teaser Challenge
Hi Steve, You can get the below query to work in 8.1.7 (not sure about previous versions) by setting the undoc parameter _new_connect_by_enabled = true and can be set for a session SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid You can also use sys_connect_by_path feature in 9i which gives you the entire hierarchy path (can be used in 8i with the above undoc parameter) For eg: select sys_connect_by_path(parent_id,'/') from treenode start with parent_id = 0 connect by prior id = parent_id will give you a output like 0/1/9 0/1/2 0/1/2/4 ...etc As always setting an undoc parameter is not advisable unless instructed by Oracle support :-) Hope this helps. Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm - The holy hand grenade of email services -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
I'm seeing some very strange results using _new_connect_by_enabled = true Login to the database, run these 2 commands: alter session set _new_connect_by_enabled = true; 1 SELECT * 2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* order by parentid, nodeorder 11:22:48 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. 11:22:49 rsysdevdb.radisys.com - jkstill@dv01 SQL These are the expected results. Now I comment out the 'FROM' clause, and add a new FROM and ORDER by: SELECT * -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) from treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid order by parentid, nodeorder; Here are the results: SELECT * 2 -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 from treenode 4 START WITH parentid=0 5 CONNECT BY PRIOR ID = parentid 6* order by parentid, nodeorder 11:24:21 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. 11:24:22 rsysdevdb.radisys.com - jkstill@dv01 SQL Hey look! The output is working just the way we would like, without the inline view. Now, remove the commented out line: SELECT * from treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid order by parentid, nodeorder; And the resulting output: 1 SELECT * 2 from treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* order by parentid, nodeorder 11:25:12 rsysdevdb.radisys.com - jkstill@dv01 SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 7 1 2 3rd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 10 3 1 nested folder2.2.1 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 10 2 nested folder2.2.2 11 rows selected. 11:25:13 rsysdevdb.radisys.com - jkstill@dv01 SQL Interesting, no? Jared Madhavan Amruthur [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 08:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: SQL Brain Teaser Challenge Hi Steve, You can get the below query to work in 8.1.7 (not sure about previous versions) by setting the undoc parameter _new_connect_by_enabled = true and can be set for a session SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid You can also use sys_connect_by_path feature in 9i which gives you the entire hierarchy path (can be used in 8i with the above undoc parameter) For eg: select sys_connect_by_path(parent_id,'/') from treenode start with parent_id = 0 connect by prior id = parent_id will give you a output like 0/1/9 0/1/2 0/1/2/4 ...etc As always setting an undoc parameter is not advisable unless instructed by Oracle support :-) Hope this helps. Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm - The holy hand grenade of email services -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: SQL Brain Teaser Challenge
Title: RE: SQL Brain Teaser Challenge SELECT LEVEL, treenode.* FROM treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid ORDER SIBLINGS BY PARENTid , nodeorder Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Orr, Steve [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Subject: SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentid number not null, nodeorder number not null, description varchar2(20) null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: SQL Brain Teaser Challenge
I get an error on 8.1.7.2. Is siblings new? SQL l 1 SELECT LEVEL, treenode.* 2FROM treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* ORDER SIBLINGS BY PARENTid , nodeorder SQL / ORDER SIBLINGS BY PARENTid , nodeorder * ERROR at line 5: ORA-00924: missing BY keyword -Original Message- Sent: Tuesday, November 05, 2002 11:02 AM To: '[EMAIL PROTECTED]'; Orr, Steve SELECT LEVEL, treenode.* FROM treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid ORDER SIBLINGS BY PARENTid , nodeorder Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Brain Teaser Challenge
Does an in-line view do the trick? select * from (select id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid) order by nodeorder; Jay [EMAIL PROTECTED] 11/05/02 12:24PM Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Hostetter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Brain Teaser Challenge
Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 09:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Title: RE: SQL Brain Teaser Challenge Steve, I'm busted! You caught me cheating. Still you must admit it was an interesting use of TRANSLATE given the data in the DESCRIPTION field. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Orr, Steve [SMTP:[EMAIL PROTECTED]] Well it worked but it doesn't follow the rules. The description can change so it should not be sorted on. Consider this: update treenode set description='2nd item, 3rd folder' where id=8; select * from treenode order by translate(description || 0, '0123456789abcdefghijklmnopqrstuvwxyz ', '0123456789'); ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 8 7 1 2nd item, 3rd folder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 9 rows selected. -Original Message- From: Whittle Jerome Contr NCI [mailto:[EMAIL PROTECTED]] select * from treenode order by translate(description || 0, '0123456789abcdefghijklmnopqrstuvwxyz ', '0123456789') Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Orr, Steve [SMTP:[EMAIL PROTECTED]] Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentid number not null, nodeorder number not null, description varchar2(20) null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana
Re: SQL Brain Teaser Challenge
Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 09:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
I love a good challenge. Since you cannot sort on a hierarchical query, you have to use an inline query... select id, parentid, nodeorder, description from (select id, parentid, nodeorder, description from treenod start with parentid=0 connect by prior id = parentid) order by parentid, nodeorder; ID PARENTID NODEORDER DESCRIPTION - -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 7 1 2 3rd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 Dan Fink -Original Message- Sent: Tuesday, November 05, 2002 10:24 AM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Title: RE: SQL Brain Teaser Challenge select * from treenode order by translate(description || 0, '0123456789abcdefghijklmnopqrstuvwxyz ', '0123456789') Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Orr, Steve [SMTP:[EMAIL PROTECTED]] Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentid number not null, nodeorder number not null, description varchar2(20) null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana
RE: SQL Brain Teaser Challenge
How about... SELECT t.id , t.parentid , t.nodeorder , t.description FROM treenode t CONNECT BY t.parentid = PRIOR t.id START WITH t.description = 'top folder' ORDER BY NVL ( TRIM ( TRANSLATE ( LOWER (t.description) , 'abcdefghijklmnopqrstuvwxyz' , ' ' ) ) , 0 ); Regards! Greg -Original Message- Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mirsky, Greg INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Title: RE: SQL Brain Teaser Challenge Steve, I did try it ... oraclei@rhea-ACPT1 sys SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 5 14:18:02 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected. SQL conn system Enter password: Connected. SQL SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Orr, Steve [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 05, 2002 2:15 PM To: Jamadagni, Rajendra; '[EMAIL PROTECTED]' Subject: RE: SQL Brain Teaser Challenge Nope... SQL l 1 SELECT * 2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4* CONNECT BY PRIOR ID = parentid SQL / FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) * ERROR at line 2: ORA-01472: cannot use CONNECT BY on view with DISTINCT, GROUP BY, etc. I supplied the DDL/DML so you could test on your own systems. -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 05, 2002 12:07 PM To: '[EMAIL PROTECTED]'; Orr, Steve Subject: RE: SQL Brain Teaser Challenge SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid Does this work? Also see http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:921234804 9 Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: SQL Brain Teaser Challenge
Title: RE: SQL Brain Teaser Challenge Whoa... A query that works on 9i but fails on 8i !!! -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Tuesday, November 05, 2002 12:19 PMTo: Orr, Steve; '[EMAIL PROTECTED]'Subject: RE: SQL Brain Teaser Challenge Steve, I did try it ... oraclei@rhea-ACPT1 sys SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 5 14:18:02 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected. SQL conn system Enter password: Connected. SQL SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Orr, Steve [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 05, 2002 2:15 PM To: Jamadagni, Rajendra; '[EMAIL PROTECTED]' Subject: RE: SQL Brain Teaser Challenge Nope... SQL l 1 SELECT * 2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4* CONNECT BY PRIOR ID = parentid SQL / FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) * ERROR at line 2: ORA-01472: cannot use CONNECT BY on view with DISTINCT, GROUP BY, etc. I supplied the DDL/DML so you could test on your own systems. -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 05, 2002 12:07 PM To: '[EMAIL PROTECTED]'; Orr, Steve Subject: RE: SQL Brain Teaser Challenge SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid Does this work? Also see http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:921234804 9 Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art!
RE: SQL Brain Teaser Challenge
You still haven't answered the challenge... your results are in a different order than the desired results I gave at the bottom. The idea is to have the children appearing immediately after the parents in the correct order. -Original Message- Sent: Tuesday, November 05, 2002 11:57 AM To: '[EMAIL PROTECTED]'; Orr, Steve I love a good challenge. Since you cannot sort on a hierarchical query, you have to use an inline query... select id, parentid, nodeorder, description from (select id, parentid, nodeorder, description from treenod start with parentid=0 connect by prior id = parentid) order by parentid, nodeorder; ID PARENTID NODEORDER DESCRIPTION - -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 7 1 2 3rd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 Dan Fink -Original Message- Sent: Tuesday, November 05, 2002 10:24 AM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Well it worked but it doesn't follow the rules. The description can change so it should not be sorted on. Consider this: update treenode set description='2nd item, 3rd folder' where id=8; select * from treenode order by translate(description || 0, '0123456789abcdefghijklmnopqrstuvwxyz ', '0123456789'); ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 8 7 1 2nd item, 3rd folder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 9 rows selected. -Original Message- Sent: Tuesday, November 05, 2002 11:52 AM To: [EMAIL PROTECTED] Cc: Orr, Steve select * from treenode order by translate(description || 0, '0123456789abcdefghijklmnopqrstuvwxyz ', '0123456789') Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Title: RE: SQL Brain Teaser Challenge SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid Does this work? Also see http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:9212348049 Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: SQL Brain Teaser Challenge
Nope... (select id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid) order by nodeorder; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 5 7 0 folder 3 item 1 8 7 1 2nd item, 3rd folder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 7 1 2 3rd subfolder 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 -Original Message- Sent: Tuesday, November 05, 2002 11:52 AM To: [EMAIL PROTECTED]; Orr, Steve Does an in-line view do the trick? select * from (select id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid) order by nodeorder; Jay [EMAIL PROTECTED] 11/05/02 12:24PM Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send
RE: SQL Brain Teaser Challenge
Nope... SQL l 1 SELECT * 2FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4* CONNECT BY PRIOR ID = parentid SQL / FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) * ERROR at line 2: ORA-01472: cannot use CONNECT BY on view with DISTINCT, GROUP BY, etc. I supplied the DDL/DML so you could test on your own systems. -Original Message- Sent: Tuesday, November 05, 2002 12:07 PM To: '[EMAIL PROTECTED]'; Orr, Steve SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid Does this work? Also see http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:921234804 9 Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Hey Raj, Ever paranoid of a ruse I had to see for myself that your query worked on O9i and not O8i. I cranked up my O9i test server and confirmed it does indeed work on 9i and not 8i. (See below.) I'm still looking for a way to get this to work on O8i. In the meantime I'll submit a TAR but I hate it when the solution is to upgrade to the next version. Steve --- SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 5 04:58:14 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production you are logged into RAC1. RAC1.SYS.SQLSELECT * 2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid ; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 11 rows selected. -Original Message- Sent: Tuesday, November 05, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Steve, I did try it ... oraclei@rhea-ACPT1 sys SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 5 14:18:02 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected. SQL conn system Enter password: Connected. SQL SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid SQL / ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, November 05, 2002 2:15 PM To: Jamadagni, Rajendra; '[EMAIL PROTECTED]' Nope... SQL l 1 SELECT * 2FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) 3 START WITH parentid=0 4* CONNECT BY PRIOR ID = parentid SQL / FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) * ERROR at line 2: ORA-01472: cannot use CONNECT BY on view with DISTINCT, GROUP BY, etc. I supplied the DDL/DML so you could test on your own systems. -Original Message- Sent: Tuesday, November 05, 2002 12:07 PM To: '[EMAIL PROTECTED]'; Orr, Steve SELECT * FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder) START WITH parentid=0 CONNECT BY PRIOR ID = parentid Does this work? Also see http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:921234804 9 Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Well it works but your query assumes knowledge of the tree- that it will always only have 3 levels. Consider when I add the following 2 rows: insert into treenode values(10,3,1,'nested folder2.2.1'); insert into treenode values(11,10,2,'nested folder2.2.2'); Now it fails. SORRY I wasn't clear on this part of the rules/spec. :-( We should be able to add nodes and levels. We should also be able update nodes to different parents and their children and children's children (etc) should automatically following them around on the tree. Any other ideas? Steve -Original Message- Sent: Tuesday, November 05, 2002 12:20 PM To: [EMAIL PROTECTED] Cc: Orr, Steve Importance: High Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 09:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com --
RE: SQL Brain Teaser Challenge
and i got this to work: SQL select * from treenode; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. /cheating with the order of the insert statements ;-) i had always thought the parentid was the way to go when representing trees in the database, but Celko describes the Nested-Set Model of Trees alternative in this ancient article: http://www.dbmsmag.com/9603d06.html makes your problem trivial. anyway, thanks for the post Steve. learned something today - i'm going home. -Original Message- Sent: Tuesday, November 05, 2002 1:15 PM To: Multiple recipients of list ORACLE-L Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 09:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To
RE: SQL Brain Teaser Challenge
I was also able to confirm this works on O9i. -Original Message- Sent: Tuesday, November 05, 2002 11:14 AM To: Multiple recipients of list ORACLE-L I get an error on 8.1.7.2. Is siblings new? SQL l 1 SELECT LEVEL, treenode.* 2FROM treenode 3 START WITH parentid=0 4 CONNECT BY PRIOR ID = parentid 5* ORDER SIBLINGS BY PARENTid , nodeorder SQL / ORDER SIBLINGS BY PARENTid , nodeorder * ERROR at line 5: ORA-00924: missing BY keyword -Original Message- Sent: Tuesday, November 05, 2002 11:02 AM To: '[EMAIL PROTECTED]'; Orr, Steve SELECT LEVEL, treenode.* FROM treenode START WITH parentid=0 CONNECT BY PRIOR ID = parentid ORDER SIBLINGS BY PARENTid , nodeorder Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services
RE: SQL Brain Teaser Challenge
Okay, my answer was almost correct (almost correct = wrong). Jared's answer is right on, given the current data set. What happens when the data is changed? Does ID have meaning or is it the sequence in which the row was added? NODEORDER is 'sequential', but the starting values vary within the parent node (sometimes start with 1, others with 0). Old college trick, instead of answering the question, you challenge the validity of the question. Can you use sql only or assume sql*plus is available? -Original Message- Sent: Tuesday, November 05, 2002 10:24 AM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Other posts should answer most of your questions. Jared's answer assumes only three levels and doesn't work when we add levels or branches to the tree. ID is a system generated key and has no meaning. SQL only, no SQL*Plus stuff. NODEORDER only refers to the sort within a node because you should not have to resequence all the data just to add a node, change the sort within a node or move a node to another parent. -Original Message- Sent: Tuesday, November 05, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Okay, my answer was almost correct (almost correct = wrong). Jared's answer is right on, given the current data set. What happens when the data is changed? Does ID have meaning or is it the sequence in which the row was added? NODEORDER is 'sequential', but the starting values vary within the parent node (sometimes start with 1, others with 0). Old college trick, instead of answering the question, you challenge the validity of the question. Can you use sql only or assume sql*plus is available? -Original Message- Sent: Tuesday, November 05, 2002 10:24 AM To: Multiple recipients of list ORACLE-L Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: SQL Brain Teaser Challenge
I wondered if someone would try that. :-) The link you provided is interesting but I don't think it fits the needs as regards being able to add and rearrange nodes. (Maybe I got confused with the little worms.) This is because the right column has to be twice the number of rows and this number would need to be updated every time you added or removed a row. -Original Message- Sent: Tuesday, November 05, 2002 12:45 PM To: Multiple recipients of list ORACLE-L and i got this to work: SQL select * from treenode; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. /cheating with the order of the insert statements ;-) i had always thought the parentid was the way to go when representing trees in the database, but Celko describes the Nested-Set Model of Trees alternative in this ancient article: http://www.dbmsmag.com/9603d06.html makes your problem trivial. anyway, thanks for the post Steve. learned something today - i'm going home. -Original Message- Sent: Tuesday, November 05, 2002 1:15 PM To: Multiple recipients of list ORACLE-L Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 09:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3
RE: SQL Brain Teaser Challenge
Steve, I'm not sure why as of yet, but I had some success by creating two segmented indexes. One on PARENTID, NODEORDER and the second on NODEORDER, PARENTID: ID PARENTID NODEORDER DESCRIPTION 1 00 top folder 9 10 1st subfolder 2 11 2nd subfolder 4 21 folder 2 item 1 3 22 folder 2 item 2 6 23 folder 2 item 3 7 12 3rd subfolder 5 70 folder 3 item 1 8 71 folder 3 item 2 Seems strange, though, and I don't have the time to research it. We had the exact same problem for our BOM structures and ended up writing a recursive PL/SQL procedure and cursors to do it. Not nice. Also, I'm using CBO on 8.1.7.4 and have analyzed the tables and indexes w/o histograms using DBMS_STATS. BTW, the explain plan is a helluva lot better with the indexes... :) HTH! GL! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Orr, Steve [mailto:sorr;rightnow.com] Sent: Tuesday, November 05, 2002 11:24 AM To: Multiple recipients of list ORACLE-L Subject: SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
actually - i think you are correct. the Nested-Set Model of Trees as Celko calls it would require more maintenance than the traditional parentid column when the tree changes... but the advantages on the query side are interesting. that nodeOrder column takes you on a ride. looks to me like your stuck with a lack of features (or a bug?) to do what you want in 8i. you could hardcode the levels like Jared did, or move to 9i and using Raj's suggestion. did you try it with a cursor? -Original Message- Sent: Tuesday, November 05, 2002 3:15 PM To: Multiple recipients of list ORACLE-L I wondered if someone would try that. :-) The link you provided is interesting but I don't think it fits the needs as regards being able to add and rearrange nodes. (Maybe I got confused with the little worms.) This is because the right column has to be twice the number of rows and this number would need to be updated every time you added or removed a row. -Original Message- Sent: Tuesday, November 05, 2002 12:45 PM To: Multiple recipients of list ORACLE-L and i got this to work: SQL select * from treenode; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. /cheating with the order of the insert statements ;-) i had always thought the parentid was the way to go when representing trees in the database, but Celko describes the Nested-Set Model of Trees alternative in this ancient article: http://www.dbmsmag.com/9603d06.html makes your problem trivial. anyway, thanks for the post Steve. learned something today - i'm going home. -Original Message- Sent: Tuesday, November 05, 2002 1:15 PM To: Multiple recipients of list ORACLE-L Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 09:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1
RE: SQL Brain Teaser Challenge
Steve, I've answered this one before (not on this list) for an Oracle 8i database... I guess the truth is that you really can't guarantee it, but it can be tricked with a hint. The trick is to access the table in correct sibling order. Create an index on the nodeorder column and then use an index hint in the query SQL create index m on treenode(nodeorder); SQL select /*+ index(t m) */ * 2 from treenode t 3 start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. How does this work? Well, when using connect by prior type SQL the rows are returned in the order in which they are input, with the except being the hierarchical sorting. By accessing the table via the index all rows with nodeorder 0 will be returned first, then nodeorder 1 second, etc. The connect-by then does it's stuff and creates the hierarchy, but by a stroke of luck you get the result you want. The danger with this? Well, I always say hints are exactly what they call themselves - hints. Oracle could choose to use a different index, or no index based on the query, number of rows, etc. If Oracle doesn't obey your hint then it won't work. Someone commented that a subquery with an order by isn't allowed - using the hint like this effectively does the same and overcomes that limitation. What do I win? Regards, Mark. PS: Since I don't have access to 9i I haven't heard of order by siblings... But it sounds like it fixes the problem correctly. Orr, Steve sorr@rightnow To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: RE: SQL Brain Teaser Challenge [EMAIL PROTECTED] om 06/11/2002 06:49 Please respond to ORACLE-L Well it works but your query assumes knowledge of the tree- that it will always only have 3 levels. Consider when I add the following 2 rows: insert into treenode values(10,3,1,'nested folder2.2.1'); insert into treenode values(11,10,2,'nested folder2.2.2'); Now it fails. SORRY I wasn't clear on this part of the rules/spec. :-( We should be able to add nodes and levels. We should also be able update nodes to different parents and their children and children's children (etc) should automatically following them around on the tree. Any other ideas? Steve -Original Message- Sent: Tuesday, November 05, 2002 12:20 PM To: [EMAIL PROTECTED] Cc: Orr, Steve Importance: High Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode
RE: SQL Brain Teaser Challenge
Very good Mark. It worked even with the new subfolders added. I have no idea WHY it worked ... but it did. ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 -Original Message- Sent: Tuesday, November 05, 2002 3:41 PM To: Multiple recipients of list ORACLE-L Steve, I've answered this one before (not on this list) for an Oracle 8i database... I guess the truth is that you really can't guarantee it, but it can be tricked with a hint. The trick is to access the table in correct sibling order. Create an index on the nodeorder column and then use an index hint in the query SQL create index m on treenode(nodeorder); SQL select /*+ index(t m) */ * 2 from treenode t 3 start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. How does this work? Well, when using connect by prior type SQL the rows are returned in the order in which they are input, with the except being the hierarchical sorting. By accessing the table via the index all rows with nodeorder 0 will be returned first, then nodeorder 1 second, etc. The connect-by then does it's stuff and creates the hierarchy, but by a stroke of luck you get the result you want. The danger with this? Well, I always say hints are exactly what they call themselves - hints. Oracle could choose to use a different index, or no index based on the query, number of rows, etc. If Oracle doesn't obey your hint then it won't work. Someone commented that a subquery with an order by isn't allowed - using the hint like this effectively does the same and overcomes that limitation. What do I win? Regards, Mark. PS: Since I don't have access to 9i I haven't heard of order by siblings... But it sounds like it fixes the problem correctly. Orr, Steve sorr@rightnow To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: RE: SQL Brain Teaser Challenge [EMAIL PROTECTED] om 06/11/2002 06:49 Please respond to ORACLE-L Well it works but your query assumes knowledge of the tree- that it will always only have 3 levels. Consider when I add the following 2 rows: insert into treenode values(10,3,1,'nested folder2.2.1'); insert into treenode values(11,10,2,'nested folder2.2.2'); Now it fails. SORRY I wasn't clear on this part of the rules/spec. :-( We should be able to add nodes and levels. We should also be able update nodes to different parents and their children and children's children (etc) should automatically following them around on the tree. Any other ideas? Steve -Original Message- Sent: Tuesday, November 05, 2002 12:20 PM To: [EMAIL PROTECTED] Cc: Orr, Steve Importance: High Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05
RE: SQL Brain Teaser Challenge
WOW. This actually works quite well!!! All this business is for generating an HTML tree navigation object using SQL, Perl-CGI, and javascript (from www.treeview.net) and it's critical that everything appear in the right order or the javascript will fail miserably. I hate to be dependent on indexes for sorts. If an index goes south or another is added that changes the results the whole app could come crashing down. But it's not the first time I'd be stuck with a fragile solution that works and I don't like it anyway. Thanks, Steve -Original Message- Sent: Tuesday, November 05, 2002 2:33 PM To: '[EMAIL PROTECTED]' Cc: Orr, Steve Steve, I'm not sure why as of yet, but I had some success by creating two segmented indexes. One on PARENTID, NODEORDER and the second on NODEORDER, PARENTID: ID PARENTID NODEORDER DESCRIPTION 1 00 top folder 9 10 1st subfolder 2 11 2nd subfolder 4 21 folder 2 item 1 3 22 folder 2 item 2 6 23 folder 2 item 3 7 12 3rd subfolder 5 70 folder 3 item 1 8 71 folder 3 item 2 Seems strange, though, and I don't have the time to research it. We had the exact same problem for our BOM structures and ended up writing a recursive PL/SQL procedure and cursors to do it. Not nice. Also, I'm using CBO on 8.1.7.4 and have analyzed the tables and indexes w/o histograms using DBMS_STATS. BTW, the explain plan is a helluva lot better with the indexes... :) HTH! GL! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Orr, Steve [mailto:sorr;rightnow.com] Sent: Tuesday, November 05, 2002 11:24 AM To: Multiple recipients of list ORACLE-L Subject: SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number not null constraint pk_treenode primary key, parentidnumber not null, nodeorder number not null, description varchar2(20)null); insert into treenode values(1,0,0,'top folder'); insert into treenode values(9,1,0,'1st subfolder'); insert into treenode values(7,1,2,'3rd subfolder'); insert into treenode values(2,1,1,'2nd subfolder'); insert into treenode values(8,7,1,'folder 3 item 2'); insert into treenode values(6,2,3,'folder 2 item 3'); insert into treenode values(5,7,0,'folder 3 item 1'); insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1'); - Here's the data presented hierachically without the desired sort: select * from treenode start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 7 1 2 3rd subfolder 8 7 1 folder 3 item 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
What do I win? This was stated in the very first post... kudos. :-) At the moment you and Rich Jesse are tied but I'm still not very pleased with the solution. But unless somebody comes up with something better I'll box you up some kudos for shipping. (I afraid to ask but what are kudos anyway?) Thanks. Tentatively yours, Steve -Original Message- Sent: Tuesday, November 05, 2002 2:41 PM To: Multiple recipients of list ORACLE-L Steve, I've answered this one before (not on this list) for an Oracle 8i database... I guess the truth is that you really can't guarantee it, but it can be tricked with a hint. The trick is to access the table in correct sibling order. Create an index on the nodeorder column and then use an index hint in the query SQL create index m on treenode(nodeorder); SQL select /*+ index(t m) */ * 2 from treenode t 3 start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. How does this work? Well, when using connect by prior type SQL the rows are returned in the order in which they are input, with the except being the hierarchical sorting. By accessing the table via the index all rows with nodeorder 0 will be returned first, then nodeorder 1 second, etc. The connect-by then does it's stuff and creates the hierarchy, but by a stroke of luck you get the result you want. The danger with this? Well, I always say hints are exactly what they call themselves - hints. Oracle could choose to use a different index, or no index based on the query, number of rows, etc. If Oracle doesn't obey your hint then it won't work. Someone commented that a subquery with an order by isn't allowed - using the hint like this effectively does the same and overcomes that limitation. What do I win? Regards, Mark. PS: Since I don't have access to 9i I haven't heard of order by siblings... But it sounds like it fixes the problem correctly. Orr, Steve sorr@rightnow To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: RE: SQL Brain Teaser Challenge [EMAIL PROTECTED] om 06/11/2002 06:49 Please respond to ORACLE-L Well it works but your query assumes knowledge of the tree- that it will always only have 3 levels. Consider when I add the following 2 rows: insert into treenode values(10,3,1,'nested folder2.2.1'); insert into treenode values(11,10,2,'nested folder2.2.2'); Now it fails. SORRY I wasn't clear on this part of the rules/spec. :-( We should be able to add nodes and levels. We should also be able update nodes to different parents and their children and children's children (etc) should automatically following them around on the tree. Any other ideas? Steve -Original Message- Sent: Tuesday, November 05, 2002 12:20 PM To: [EMAIL PROTECTED] Cc: Orr, Steve Importance: High Steve, This works for me. Jared col nodelevel noprint col parent noprint col child noprint select a.nodelevel , a.id id , a.parentid , a.nodeorder , a.description , decode(c.children,null,'N','Y') parent , decode(p.children,null,'Y','N') child from ( select level nodelevel, id, parentid, nodeorder, description from treenode start with parentid=0 connect by prior id = parentid ) a, ( select parentid, count(*) children from treenode b group by parentid ) c, ( select parentid, count(*) children from treenode d group by parentid ) p where a.id = c.parentid(+) and a.id = p.parentid(+) order by decode(parent -- is a parent , 'Y', nodelevel * id -- is a child , 'N', nodelevel * parentid + nodeorder ) / Orr, Steve [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2002 09:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL Brain Teaser Challenge Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode ( id number
RE: SQL Brain Teaser Challenge
Kevin, Like I said - as long as the table access is via the index then all rows are returned in nodeorder (sorted globally). The connect-by clause then orders the result set hierarchically. When searching for children of a parent it will find them in correct nodeorder order. It works for an infinite number of depths, although I believe connect-by only works to 256 levels anyway. The most likely problem to encounter relates to adding indexes to the id and parentid columns - necessary if the tree of going to be much more than (maybe) 1000 rows. In this instance a concatenated index of parentid,nodeorder would probably be best, although I've never really tested that out. As a side note: The first person I solved this for wanted all children of the parent sorted alphabetically - this was achieved by indexing the equivalent of the description column - I can imagine there are several applications of this query. Kevin Lange [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] om cc: Sent by: Subject: RE: SQL Brain Teaser Challenge [EMAIL PROTECTED] om 06/11/2002 09:08 Please respond to ORACLE-L Very good Mark. It worked even with the new subfolders added. I have no idea WHY it worked ... but it did. ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 10 3 1 nested folder2.2.1 11 10 2 nested folder2.2.2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 -Original Message- Sent: Tuesday, November 05, 2002 3:41 PM To: Multiple recipients of list ORACLE-L Steve, I've answered this one before (not on this list) for an Oracle 8i database... I guess the truth is that you really can't guarantee it, but it can be tricked with a hint. The trick is to access the table in correct sibling order. Create an index on the nodeorder column and then use an index hint in the query SQL create index m on treenode(nodeorder); SQL select /*+ index(t m) */ * 2 from treenode t 3 start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. How does this work? Well, when using connect by prior type SQL the rows are returned in the order in which they are input, with the except being the hierarchical sorting. By accessing the table via the index all rows with nodeorder 0 will be returned first, then nodeorder 1 second, etc. The connect-by then does it's stuff and creates the hierarchy, but by a stroke of luck you get the result you want. The danger
RE: SQL Brain Teaser Challenge
Steve, I thought kudos was the codename for the latest and greatest Palm Pilot. : -) As I said - it's not a nice solution, it has it's limits, but it works on Oracle 8i (and should work on other versions as well) Regards, Mark. PS: For the sake of completeness - the parentid of node 11 should be 3 judging by the description. Orr, Steve sorr@rightnow To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: RE: SQL Brain Teaser Challenge [EMAIL PROTECTED] om 06/11/2002 09:23 Please respond to ORACLE-L What do I win? This was stated in the very first post... kudos. :-) At the moment you and Rich Jesse are tied but I'm still not very pleased with the solution. But unless somebody comes up with something better I'll box you up some kudos for shipping. (I afraid to ask but what are kudos anyway?) Thanks. Tentatively yours, Steve -Original Message- Sent: Tuesday, November 05, 2002 2:41 PM To: Multiple recipients of list ORACLE-L Steve, I've answered this one before (not on this list) for an Oracle 8i database... I guess the truth is that you really can't guarantee it, but it can be tricked with a hint. The trick is to access the table in correct sibling order. Create an index on the nodeorder column and then use an index hint in the query SQL create index m on treenode(nodeorder); SQL select /*+ index(t m) */ * 2 from treenode t 3 start with parentid=0 connect by prior id = parentid; ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. How does this work? Well, when using connect by prior type SQL the rows are returned in the order in which they are input, with the except being the hierarchical sorting. By accessing the table via the index all rows with nodeorder 0 will be returned first, then nodeorder 1 second, etc. The connect-by then does it's stuff and creates the hierarchy, but by a stroke of luck you get the result you want. The danger with this? Well, I always say hints are exactly what they call themselves - hints. Oracle could choose to use a different index, or no index based on the query, number of rows, etc. If Oracle doesn't obey your hint then it won't work. Someone commented that a subquery with an order by isn't allowed - using the hint like this effectively does the same and overcomes that limitation. What do I win? Regards, Mark. PS: Since I don't have access to 9i I haven't heard of order by siblings... But it sounds like it fixes the problem correctly. Orr, Steve sorr@rightnow To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: RE: SQL Brain Teaser Challenge [EMAIL PROTECTED] om 06/11/2002 06:49 Please respond
Re: SQL Brain Teaser Challenge
Orr, Steve wrote: What do I win? This was stated in the very first post... kudos. :-) At the moment you and Rich Jesse are tied but I'm still not very pleased with the solution. But unless somebody comes up with something better I'll box you up some kudos for shipping. (I afraid to ask but what are kudos anyway?) Thanks. Tentatively yours, Steve Steve, I have a solution which doesn't rely on hints, but I am not very satisfied with it either. It relies on a function, and performance will be likely to be dismal if your tree grows big. Here is the function : create or replace function tree_rank(p_id in number) return number is n_rank number; begin select sum(nodeorder * power(10, -1 * level)) into n_rank from treenode where id in (select id from treenode connect by id = prior parentid start with id = p_id) connect by parentid = prior id start with id = 1; return n_rank; end; / (double 'CONNECT BY', ouch). Note that if you expect more than 10 items per level, you should use somthing bigger than 10 in the power function. However : SQL select * from treenode 2 order by tree_rank(id); ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
I have a solution which doesn't rely on hints, but I am not very satisfied with it either. Innovative nonetheless. Another cool way to skin this cat. Thanks! Steve -Original Message- Sent: Tuesday, November 05, 2002 5:14 PM To: Multiple recipients of list ORACLE-L Importance: High Orr, Steve wrote: What do I win? This was stated in the very first post... kudos. :-) At the moment you and Rich Jesse are tied but I'm still not very pleased with the solution. But unless somebody comes up with something better I'll box you up some kudos for shipping. (I afraid to ask but what are kudos anyway?) Thanks. Tentatively yours, Steve Steve, I have a solution which doesn't rely on hints, but I am not very satisfied with it either. It relies on a function, and performance will be likely to be dismal if your tree grows big. Here is the function : create or replace function tree_rank(p_id in number) return number is n_rank number; begin select sum(nodeorder * power(10, -1 * level)) into n_rank from treenode where id in (select id from treenode connect by id = prior parentid start with id = p_id) connect by parentid = prior id start with id = 1; return n_rank; end; / (double 'CONNECT BY', ouch). Note that if you expect more than 10 items per level, you should use somthing bigger than 10 in the power function. However : SQL select * from treenode 2 order by tree_rank(id); ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL Brain Teaser Challenge
Steve: Unfortunately, I didn't have time to experiment with this Teaser, but I was glad (real glad) to see that several great minds made the attempt. I could use all of you to help teach my students how to use their minds to solve problems like this. Ok. Now back to work folks. See you at OracleWorld Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Tuesday, November 05, 2002 7:43 PM To: Multiple recipients of list ORACLE-L Subject:RE: SQL Brain Teaser Challenge I have a solution which doesn't rely on hints, but I am not very satisfied with it either. Innovative nonetheless. Another cool way to skin this cat. Thanks! Steve -Original Message- Sent: Tuesday, November 05, 2002 5:14 PM To: Multiple recipients of list ORACLE-L Importance: High Orr, Steve wrote: What do I win? This was stated in the very first post... kudos. :-) At the moment you and Rich Jesse are tied but I'm still not very pleased with the solution. But unless somebody comes up with something better I'll box you up some kudos for shipping. (I afraid to ask but what are kudos anyway?) Thanks. Tentatively yours, Steve Steve, I have a solution which doesn't rely on hints, but I am not very satisfied with it either. It relies on a function, and performance will be likely to be dismal if your tree grows big. Here is the function : create or replace function tree_rank(p_id in number) return number is n_rank number; begin select sum(nodeorder * power(10, -1 * level)) into n_rank from treenode where id in (select id from treenode connect by id = prior parentid start with id = p_id) connect by parentid = prior id start with id = 1; return n_rank; end; / (double 'CONNECT BY', ouch). Note that if you expect more than 10 items per level, you should use somthing bigger than 10 in the power function. However : SQL select * from treenode 2 order by tree_rank(id); ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 9 rows selected. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
