| 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
"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)
(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: sfaroult_at_oriole.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 - 18:13:39 CST
 |
 |