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, > 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: 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).
