Oracle-L: RE: SQL Brain Teaser Challenge

From: Orr, Steve <sorr_at_rightnow.com>
Date: Tue, 05 Nov 2002 13:06:56 -0800
Message-ID: <F001.004FC3D3.20021105130656@fatcity.com>

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,
	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: sorr_at_rightnow.com

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: ListGuru_at_fatcity.com (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: Dan.Fink_at_mdx.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------