Oracle-L: RE: SQL Brain Teaser Challenge

From: Orr, Steve <sorr_at_rightnow.com>
Date: Tue, 05 Nov 2002 10:13:32 -0800
Message-ID: <F001.004FBEF2.20021105101332@fatcity.com>

I get an error on 8.1.7.2. Is "siblings" new?

SQL> l
  1 SELECT LEVEL, treenode.*
  2 FROM 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: 'ORACLE-L_at_fatcity.com'; 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, 
        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: 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
---------------------------------------------------------------------