Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL Brain Teaser Challenge

Re: SQL Brain Teaser Challenge

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Tue, 05 Nov 2002 16:13:39 -0800
Message-ID: <F001.004FC5DA.20021105161339@fatcity.com>


"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: 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US