> 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).
