| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Brain Teaser Challenge
Steve,
I thought kudos was the codename for the latest and greatest Palm Pilot. : -)
As I said - it's not a nice solution, it has it's limits, but it works on Oracle 8i (and should work on other versions as well)
Regards,
Mark.
PS: For the sake of completeness - the parentid of node 11 should be 3 judging by the description.
"Orr, Steve"
<sorr_at_rightnow To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
.com> cc:
Sent by: Subject: RE: SQL Brain Teaser Challenge
root_at_fatcity.c
om
06/11/2002
09:23
Please respond
to ORACLE-L
> What do I win?
This was stated in the very first post... kudos. :-)
At the moment you and Rich Jesse are tied but I'm still not very pleased
with the solution. But unless somebody comes up with something better I'll
box you up some kudos for shipping. (I afraid to ask but what are kudos
anyway?)
Thanks.
Tentatively yours,
Steve
-----Original Message-----
Sent: Tuesday, November 05, 2002 2: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_at_rightnow To: Multiple recipients of
list
.com> cc:
Sent by: Subject: RE: SQL Brain Teaser
Challenge
root_at_fatcity.c
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: oracle-l_at_fatcity.com
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') childfrom (
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
"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');
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 --------------------------------------------------------------------- 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). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>> 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: mrichard_at_transurban.com.au 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 --------------------------------------------------------------------- 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). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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: mrichard_at_transurban.com.au 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 - 17:14:02 CST
 |
 |