Oracle-L: Re: SQL Brain Teaser Challenge

From: <Jared.Still_at_radisys.com>
Date: Tue, 05 Nov 2002 11:24:46 -0800
Message-ID: <F001.004FC187.20021105112446@fatcity.com>

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" <sorr_at_rightnow.com>
Sent by: root_at_fatcity.com
11/05/2002 09:24 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        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,
                 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: 
  INET: Jared.Still_at_radisys.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).

Received on Tue Nov 05 2002 - 13:24:46 CST