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]>
.com> cc:
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 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).
--
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).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Richard
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: Kevin Lange
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).
