Steve:
Unfortunately, I didn't have time to experiment with this Teaser, but I
was glad (real glad) to see that several "great" minds made the attempt. I
could use all of you to help teach my students how to use their minds to
solve problems like this. Ok. Now back to work folks. See you at
OracleWorld
Thank You
Stephen P. Karniotis
Product Architect
Compuware Corporation
Direct: (248) 865-4350
Mobile: (248) 408-2918
Email: [EMAIL PROTECTED]
Web: www.compuware.com
-----Original Message-----
Sent: Tuesday, November 05, 2002 7:43 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: SQL Brain Teaser Challenge
> I have a solution which doesn't rely on hints, but I am not very
> satisfied with it either.
Innovative nonetheless. Another cool way to skin this cat.
Thanks!
Steve
-----Original Message-----
Sent: Tuesday, November 05, 2002 5:14 PM
To: Multiple recipients of list ORACLE-L
Importance: High
"Orr, Steve" wrote:
>
> > 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
>
Steve,
I have a solution which doesn't rely on hints, but I am not very
satisfied with it either. It relies on a function, and performance will
be likely to be dismal if your tree grows big.
Here is the function :
create or replace function tree_rank(p_id in number)
return number
is
n_rank number;
begin
select sum(nodeorder * power(10, -1 * level))
into n_rank
from treenode
where id in (select id from treenode
connect by id = prior parentid
start with id = p_id)
connect by parentid = prior id
start with id = 1;
return n_rank;
end;
/
(double 'CONNECT BY', ouch). Note that if you expect more than 10 items
per level, you should use somthing bigger than 10 in the power function.
However :
SQL> select * from treenode
2 order by tree_rank(id);
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.
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
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).
The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Karniotis, Stephen
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).
