| 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:
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: Stephen.Karniotis_at_Compuware.com 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)
(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). -- 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). 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: Stephen_Karniotis_at_compuware.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 - 20:38:24 CST
 |
 |