Skip to Main Content
  • Questions
  • how to traverse the tree in reverse direction?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Prince.

Asked: July 22, 2002 - 9:09 pm UTC

Last updated: February 23, 2018 - 1:54 am UTC

Version: 8.1.7.4

Viewed 50K+ times! This question is

You Asked

dear Tom,

In a hierarchical query how do I traverse the tree from the branch level to the top?

Say in the emp->manager case, given the empno, I want to find his manager, manager's manager etc.

thanks,

and Tom said...

Just connect by differently.

select *
from EMP
start with EMPNO = :x
connect by prior MGR = EMPNO;

plug in the empno and there you go.

Rating

  (39 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very good. Thanks.

Prince, July 23, 2002 - 1:49 pm UTC


What about ordering?

Alla, September 15, 2003 - 11:48 am UTC

Extra question on ordering.

If I run the query "as is", it'll show me the employee first, his manager next etc

I would like to see the "big boss" first and my employee last

The only thing I could think of was "order by rownum desc"

Do you see any problems with this approach???

Thanks in advance for your help

Alla

Tom Kyte
September 15, 2003 - 2:30 pm UTC

i'd probably use "level" instead but yes, should work. I'd use an inline view to be safe about it:

select *
from (
select emp.* , rownum r
from EMP
start with EMPNO = :x
connect by prior MGR = EMPNO
)
order by r desc;

Thanks

Alla, September 15, 2003 - 2:48 pm UTC

I would've used "level" as well, but unfortunately, there is no such a column in the table (it was designed long before my time)

Thanks a lot!

Tom Kyte
September 15, 2003 - 2:56 pm UTC

level is a psuedo column associated with connect bys! it is there like rownum is.

Thanks

Alla, September 15, 2003 - 3:08 pm UTC

Damn it, I feel so stupid :-(

Tom Kyte
September 15, 2003 - 4:16 pm UTC

don't -- i learn something new about oracle each and every day -- honest. It is a really big topic

Raghu

Raghu, September 17, 2003 - 3:12 am UTC

Hi Tom,

Here is a query.

SELECT node_id
,description
,parent_id
,level
FROM tab_nodes
START WITH node_id = 1
CONNECT BY PRIOR node_id = parent_id
ORDER SIBLINGS BY description;

This query works fine with SQL*PLUS. I am using 9i database and forms9i.
But the same query gives error when i put it in forms.
Is it that ORDER SIBLINGS BY is not allowed within forms.


Tom Kyte
September 17, 2003 - 7:10 am UTC

could be -- you can try using a record_group with "populate_group_from_query" (make sure to bind -- set up a control block and dynamically execute


select .. from tab_nodes start with node_id = :block.item1 .....


then, you can access the result set in the record group. or use EXEC_SQL.

that is what I do when the environment rejects some new sql syntax.

Can we go a bit higher?

Neolle, January 05, 2005 - 2:41 am UTC

Hi Tom,

I have a table named my_tree

SQL> select * from my_tree;
PARENT     CHILD
---------- -----------
S1         S2
           S6
S2         S3
S3         S4
           S5
           
7 rows selected    
SQL>

Is it possible to have a list of all the parents that a child has? Example for S5, the following parents will be returned (S3,S2 and S1).

One more thing if I want to return a common parent between two children. Example (S6 and S5 returns S1).

Thanks!!!! 

Tom Kyte
January 05, 2005 - 9:50 am UTC

S5 has no parents, sorry -- this does not compute.

assuming you meant to carry down the parents, so S6 -> S1 and S5 -> S3, a simple connect by 


select * 
  from my_tree
 start with child = 'S5'
connect by prior parent = child;

gives you such a list -- it is what connect by does.


ops$tkyte@ORA9IR2> select *
  2    from t
  3   start with child = 's5'
  4  connect by prior parent = child;
 
PA CH
-- --
s3 s5
s2 s3
s1 s2
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select parent
  2    from t
  3   start with child in ( 's5', 's6' )
  4  connect by prior parent = child
  5   group by parent having count(*) = 2
  6  /
 
PA
--
s1
 

Re:Can we go a bit higher?

Neolle, January 05, 2005 - 3:05 am UTC

I got it already Tom.
Thanks anyway.

re: Can we get a bit higher?

Neolle, January 06, 2005 - 2:51 am UTC

Hello Tom,
Thanks for the answers, I was also able to figure it out afterwards.
Glad to know we came up with the same solution.

However, I noticed in some of your examples you use a field named Level (1,2,3,4) in determining the closest relative of a child. Would it be possible to get the same result without using Levels?

Thanks.

Tom Kyte
January 06, 2005 - 10:45 am UTC

level is not a field, it is a builtin psuedo column assigned by the connect by processing.

not sure what examples you are talking about.

The closest ancestors named 'X'

David, March 02, 2005 - 7:25 am UTC

Hi,

I want use a "reverse" hirarchical query to find the closest ancestors named 'X' of a list of row.

This works well for a single row

select * from (
SELECT FR_ID, FR_NAME, LEVEL, rownum r1
FROM HIERAR_TABLE
where FR_NAME='X'
START WITH FR_ID = 2682200
CONNECT BY PRIOR PARENT_FR_ID = FR_ID
) where r1 = 1


But when I want to find the closest ancestors for A LIST of rows, it doesn't work (ORA-00904: "Table2"."FR_ID": invalid identifier):

select Table2.FR_ID,
(
select FR_ID from (
SELECT FR_ID, FR_NAME, LEVEL, rownum r1
FROM HIERAR_TABLE
where FR_NAME='X'
START WITH FR_ID = Table2.FR_ID
CONNECT BY PRIOR PARENT_FR_ID = FR_ID
) where r1 = 1
) from Table2 where [condition]

And this is taking way too long :

select FR_ID from (
SELECT FR_ID, FR_NAME, LEVEL, rownum r1
FROM HIERAR_TABLE
where FR_NAME='X'
START WITH FR_ID in (select Table2.FR_ID from Table2 where [condition])
CONNECT BY PRIOR PARENT_FR_ID = FR_ID
) where r1 = 1


Thanks for your help!!



Tom Kyte
March 02, 2005 - 7:48 am UTC

i cannot help, the queries return ora-942 on my system

but with scalar subqueries, the correlation names only go "one deep", you are trying two levels deep


how about one level


select max( decode(rownum,1,fr_id) )
from t
where fr_name='x'
start with fr_id = table2.fr_id
connect by prior parent_fr_id = fr_id





recursively traversing the tree with connect by

Kubilay, June 28, 2005 - 11:35 am UTC

Hi Tom

I have a question in hierarchical queries.

Can we use 'connect by' for the following tree to look for grand-grand parents, parents,
their children and the children's children (then the children
must be listed as parents) if they have any.
From the root of the tree to the bottom node.

Let me explain more:


My situation is like this:

create table loc
(
par_id number(12)
loc_id number(12),
);

insert into loc values (-1, 1);
insert into loc values (-1, 8);
insert into loc values (-1, 7);
insert into loc values (1, 12);
insert into loc values (1, 13);
insert into loc values (12,40);
insert into loc values (12,21);
insert into loc values (40,31);
insert into loc values (7, 10);
insert into loc values (10,18);


select * from loc;

PAR_ID LOC_ID
---------- ----------
-1 1
-1 8
-1 7
1 12
1 13
12 40
12 21
40 31
7 10
10 18


10 rows

Tree is like this:


-1
-------------------
| | |
1 8 7
____________ __________
| | | |
12 13 10
| |
______ 18
| |
40 21
|
31



I use connect by like this :
Which correctly gives me all parents and their children.
But it doesn't list me the grand-grand parents of the children and of the parents.


select par_id, loc_id
from loc
start with par_id in (select loc_id from loc where par_id=-1)
connect by prior loc_id=par_id


PAR_ID LOC_ID
---------- ----------
1 12
12 40
40 31
12 21
1 13
7 10
10 18



7 rows selected.


But what I want is to create a 2 column table like this:

All grand-grand parents (up to -1), the parents, and children which are parents in one column,
and all their children in another column.
Last it should show that every parent is a child of itself as well. Exactly like
below.

Kind of double checking every node if it has children and listing it, as traversing the tree.


The table I am after is like this:

PAR_ID LOC_ID
---------- ----------
1 1
1 12
1 13
1 40
1 21
1 31
8 8
7 7
7 10
7 18
12 12
12 40
12 21
12 31
40 40
40 31
21 21
31 31
13 13
10 10
10 18
18 18


Is that possible in one SQL statment with 'connect by' or a pl/sql procedure.
If a procedure then how should start.


Many thanks for all your help.

Regards

Kubilay

Tom Kyte
June 28, 2005 - 12:45 pm UTC

would the information returned by sys_connect_by_path be sufficient (all of the parents on the same row)?

recursively traversing the tree with connect by

Kubilay, June 29, 2005 - 7:50 am UTC

   Hi Tom
   
   Many Thanks for your reply!
   
   Yeah, will be sufficient, data-wise is what I want! Great!
   
   But how can I display all that in a tabular format with 2 columns.
   Column 1 all 'parents' possible, column 2 all 'children' possible.
   
   
   I get this when I run it on my test case which correctly shows all the hierarchy
   info.
   
   
    SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(loc_id, '/') "Path"
      FROM loc
      START WITH par_id = -1
      CONNECT BY PRIOR loc_id = par_id;  2    3    4
   
   Path
   --------------------------------------------------------------------------------
    /1
      /1/12
        /1/12/40
          /1/12/40/31
        /1/12/21
      /1/13
    /8
    /7
      /7/10
        /7/10/18
   
   10 rows selected.
   
   
But what I want is this:
(it is the same as abovove but with all levels in between)

    PAR_ID         LOC_ID
----------     ----------
1            1 
1            12 
1                       13 
1            40 
1            21 
1            31 
8                       8 
7            7 
7            10 
7            18 
12            12 
12            40  
12            21 
12                      31 
40            40 
40            31 
21                      21 
31                      31 
13                      13 
10            10 
10            18 
18                      18 


I am not sure if SQL can do that:

I have written the following procedure but does half of the job
it just put the grandfather of all lowest level children
and not anything in between like children who are parents
and their children. I want to go through all the levels
in between as well.

create or replace procedure loc_children as
cursor cur_locchi is select * from loc where par_id=-1;
rec_locchi loc%rowtype;
 begin
   open cur_locchi;
   loop
       fetch cur_locchi into rec_locchi;
       exit when cur_locchi%notfound;
       insert all
       into test_loc values (par_id, chi_id)
       select rec_locchi.loc_id par_id, loc_id chi_id  from loc
       start with par_id = rec_locchi.loc_id
       connect by prior loc_id=par_id;
   end loop;
   dbms_output.put_line( cur_locchi%rowcount || ' rows inserted' );
   close cur_locchi;
   commit;
end loc_children;



All it does is to fill the table like this, 
which is OK but all levels in between can't get hold of them.


SQL> select * from test_loc;

    PAR_ID     LOC_ID
---------- ----------
         1         12
         1         40
         1         31
         1         21
         1         13
         7         10
         7         18


Many thanks

Kubilay
 

Tom Kyte
June 29, 2005 - 9:14 am UTC

so, the answer is "no, it is not sufficient" really :)

here is one approach

ops$tkyte@ORA9IR2> select distinct
  2         to_number( substr(path,1,instr(path,',')-1) ) par_id,
  3         to_number( column_value ) loc_id
  4    from (
  5   SELECT substr(SYS_CONNECT_BY_PATH(loc_id, ','),2) || ',' Path
  6     FROM loc
  7  CONNECT BY PRIOR loc_id = par_id ), TABLE( str2tbl(rtrim(path,',')) )
  8    order by 1, 2
  9  /
 
    PAR_ID     LOC_ID
---------- ----------
         1          1
         1         12
         1         13
         1         21
         1         31
         1         40
         7          7
         7         10
         7         18
         8          8
        10         10
        10         18
        12         12
        12         21
        12         31
        12         40
        13         13
        18         18
        21         21
        31         31
        40         31
        40         40
 
22 rows selected.




ops$tkyte@ORA9IR2> create or replace type str2tblType as table of varchar2(30)
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return str2tblType
  2  PIPELINED
  3  as
  4      l_str      long default p_str || p_delim;
  5      l_n        number;
  6  begin
  7      loop
  8          l_n := instr( l_str, p_delim );
  9          exit when (nvl(l_n,0) = 0);
 10          pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
 11          l_str := substr( l_str, l_n+1 );
 12      end loop;
 13      return;
 14  end;
 15  /
 
Function created.


 

recursively traversing the tree with connect by

kubilay, June 29, 2005 - 9:54 am UTC

Many Thanks Tom!

I didn't know that there is function called TABLE in oracle's collections let about the PIPELINED/PIPE ROW iteration. Could never figure out that for sure and would still be trying to make that procedure work.

You are such a great Teacher!


Best Regards

Kubilay



Reader, August 11, 2006 - 4:25 pm UTC

If I have to traverse from root to branch in the EMP table, we have to put the prior clause before EMPNO.
select *
from EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR

If I have to traverse from branch to root in the emp table, we have to put the prior clause before MGR.

select *
from EMP
start with EMPNO = :x
connect by prior MGR = EMPNO;

Please explain me how to decide, where to put the PRIOR clause.

Tom Kyte
August 11, 2006 - 6:09 pm UTC

the only way to decide is to have a question to be asked.

If you want to go from manager to managed - you have to take the prior records EMPNO and find all of the records that have MGR equal to that EMPNO (take manager key and find the managed people)

If you want to go from the managed person to their manager and their managers manager and so on, you have to take the prior records MGR and find those records.


It is 100% in the question you are asking.

As it is with all SQL :)

recursively finding all parents.

Dan, August 24, 2006 - 7:42 pm UTC

Hi Tom,
This thread has been very helpful explaining
how to write hierarchical queries. But I'm stuck
trying to do the following:

Let say we have the following data set.

PARENT CHILD
---------- -----------
S1 S2
S2 S3
S3 S4
S4 S5
S5

I'd like list every parent in the tree for each child.
For example,

CHILD PARENT
---------- -----------
S5 S4
S5 S3
S5 S2
S5 S1
S4 S3
S4 S2
S4 S1
etc......


Thanks,
Dan


Tom Kyte
August 27, 2006 - 8:10 pm UTC

would sys_connect_by_path on a regular hierarchical query be sufficient?

recursively finding all parents

Dan, August 28, 2006 - 5:26 pm UTC

Yes, the sys_connect_by_path contain the correct information I'm looking for, but I'm not clear on how to get the final end results I'm looking for.

Thanks,
Daniel

Tom Kyte
August 28, 2006 - 5:37 pm UTC

My question was more along the lines of "is what sys connect by path returns more than sufficient"

do you really need it broken down like that. or is "across" better than good enough?

recusively finding all parents

Dan, August 29, 2006 - 1:52 pm UTC

Yes, I need the data in that specific format.
Thats the problem.

Thanks,
Dan

Tom Kyte
August 29, 2006 - 4:38 pm UTC

ops$tkyte%ORA10GR2> select a child, p parent
  2    from (
  3  select p, connect_by_root p a
  4    from t
  5   connect by prior p = c
  6         )
  7   where p <> a
  8  order by 1 desc, 2 desc
  9  /

CH PA
-- --
S5 S4
S5 S3
S5 S2
S5 S1
S4 S3
S4 S2
S4 S1
S3 S2
S3 S1
S2 S1

10 rows selected.


you can use sys_connect_by_path in 10gr1 and before in place of connect by root and substr off the root. 

How can I do this

Parag J Patankar, November 11, 2006 - 7:10 am UTC

Hi Tom,

I am learning connect by clause, I want to generate output using bottoms up approch or reverse order details are

create or replace view v
as
select emp.ename, emp.empno, emp.mgr, dept.dname from emp, dept
where emp.deptno = dept.deptno
/

View created.

1 select rpad('* ',2*level,'* ')||ename EmpName, dname
2 from v
3 start with mgr is null
4 connect by prior empno = mgr
5* order siblings by ename
14:18:57 SQL> /

* KING ACCOUNTING
* * BLAKE SALES
* * * ALLEN SALES
* * * JAMES SALES
* * * MARTIN SALES
* * * TURNER SALES
* * * WARD SALES
* * CLARK ACCOUNTING
* * * MILLER ACCOUNTING
* * JONES RESEARCH
* * * FORD RESEARCH
* * * * SMITH RESEARCH
* * * SCOTT RESEARCH
* * * * ADAMS RESEARCH

14 rows selected.

Now I want bottoms up approch ( or reverse order ) how can I do this without using in A VIEW.

Desired O/P like 

* * * * SMITH RESEARCH
* * * FORD RESEARCH
* * * * ADAMS RESEARCH
* * * SCOTT RESEARCH
* * JONES RESEARCH
* * * ALLEN SALES
* * * JAMES SALES
* * * MARTIN SALES
* * * TURNER SALES
* * * WARD SALES
* * BLAK SALES
* * * MILLER ACCOUNTING
* * CLARK ACCOUNTING
* KING ACCOUNTING


Kindly help.

thanks & regards
PJP 



 

Tom Kyte
November 11, 2006 - 8:20 am UTC

doesn't seem like an entirely "reasonable real world" sort of thing, but...

scott%ORA10GR2> select a.*, rownum
2 from (
3 select rpad('* ',2*level,'* ')||ename EmpName, dname
4 from v
5 start with mgr is null
6 connect by prior empno = mgr
7 order siblings by ename
8 ) a
9 order by rownum DESC;

EMPNAME DNAME ROWNUM
-------------------- -------------- ----------
* * * * ADAMS RESEARCH 14
* * * SCOTT RESEARCH 13
* * * * SMITH RESEARCH 12
* * * FORD RESEARCH 11
* * JONES RESEARCH 10
* * * MILLER ACCOUNTING 9
* * CLARK ACCOUNTING 8
* * * WARD SALES 7
* * * TURNER SALES 6
* * * MARTIN SALES 5
* * * JAMES SALES 4
* * * ALLEN SALES 3
* * BLAKE SALES 2
* KING ACCOUNTING 1

14 rows selected.


Traverse from leaf to root, print from root to leaf !!!

yg, November 14, 2006 - 9:15 pm UTC

Hi Tom,

Heres the table & data -

CREATE TABLE TEST1
(
"ID1" NUMBER,
"NAME" VARCHAR2(100 BYTE),
"ID2" NUMBER
);

Insert into TEST1 ("ID1","NAME") values ('5','VP21');
Insert into TEST1 ("ID1","NAME") values ('4','VP11');
Insert into TEST1 ("ID1","NAME") values ('3','SVP2');
Insert into TEST1 ("ID1","NAME") values ('2','SVP1');
Insert into TEST1 ("ID1","NAME") values ('1','Boss');
Insert into TEST1 ("ID1","NAME") values ('7','EMP1111');
Insert into TEST1 ("ID1","NAME") values ('6','DIR111');

I run the following query -

select distinct sys_connect_by_path(name, '/') "path", level
from test1 start with id1 in (7, 3)
connect by prior id2 = id1
order by level;

to get -

/EMP1111 1
/SVP2 1
/EMP1111/DIR111 2
/SVP2/Boss 2
/EMP1111/DIR111/VP11 3
/EMP1111/DIR111/VP11/SVP1 4
/EMP1111/DIR111/VP11/SVP1/Boss 5

I need to filter and restructure the o/p to give me -
Boss/SVP1/VP11/DIR111/EMP1111
Boss/SVP2

i.e. I need to see the entries -
1. which are leaf nodes (no intermediate nodes).
2. the order of printing should be from root -> leaf node.

How can I acheive that ?

Thanks,

- yg

Tom Kyte
November 15, 2006 - 6:56 am UTC

are you on 10g or not.

Re:Traverse from leaf to root, print from root to leaf !!!

yg, November 14, 2006 - 9:27 pm UTC

The insert for the previous post should read like following -

Insert into TEST1 ("ID1","NAME","ID2") values (5,'VP21',3);
Insert into TEST1 ("ID1","NAME","ID2") values (4,'VP11',2);
Insert into TEST1 ("ID1","NAME","ID2") values (3,'SVP2',1);
Insert into TEST1 ("ID1","NAME","ID2") values (2,'SVP1',1);
Insert into TEST1 ("ID1","NAME","ID2") values (1,'Boss',null);
Insert into TEST1 ("ID1","NAME","ID2") values (7,'EMP1111',6);
Insert into TEST1 ("ID1","NAME","ID2") values (6,'DIR111',4);

Sorry abt the confusion (I exported the data in a SQL file
using SQLDeveloper and it somehow didn't insert the last col).

- yg

Re:Traverse from leaf to root, print from root to leaf !!!

yg, November 15, 2006 - 2:58 pm UTC

Yes. I'm on 10g.

Tom Kyte
November 16, 2006 - 8:19 am UTC

use connect by isroot, you can get just the roots.

new function.

To: yg

Tom Fox, November 15, 2006 - 7:07 pm UTC

Tom can surely walk circles around me, but I'm going to take a stab anyway, since my role is transitioning to more SQL query oriented versus DBA.

To reverse the tree, flip the values in the CONNECT BY clause (from id2 = id1 to id1 = id2). Now, use an inline view:

select path, max(level)
from
(select distinct sys_connect_by_path(name, '/') "path", level, id1
from test1 start with id1 in (7, 3)
connect by prior id1 = id2
order by level)
where id1 in (7,3)

To: yg

Tom Fox, November 15, 2006 - 7:33 pm UTC

Wait a sec, that query won't work. How about this?

select path, level
from
(select distinct sys_connect_by_path(name, '/') "path", level, connect_by_isleaf "isleaf"
from test1 start with id1 in (7, 3)
connect by prior id1 = id2
order by level)
where isleaf = 1;



To: Tom Fox

yg, November 15, 2006 - 7:46 pm UTC

Thanks Tom for taking time to answer.

The query you suggested errors out -

Error starting at line 1 in command:
select path, level
from
(select distinct sys_connect_by_path(name, '/') "path", level, connect_by_isleaf "isleaf"
from test1 start with id1 in (7, 3)
connect by prior id1 = id2
order by level)
where isleaf = 1
Error report:
SQL Error: ORA-00904: "ISLEAF": invalid identifier



Tom Kyte
November 16, 2006 - 3:03 pm UTC

where "isleaf" = 1

So was my query correct too?

Tom Fox, November 16, 2006 - 4:02 pm UTC

Just looking for confirmation that I was actually able to put something together correctly in SQL. Boy, is this a massive world to learn.

Tom Kyte
November 16, 2006 - 4:09 pm UTC

don't know :) didn't read it, just knew why isleaf was not 'referenced' correctly.

yes, it is large.

rain

rain, November 30, 2006 - 10:10 pm UTC

Hi,Tom
When I use Hierarchical SQL combine text like:
select sys_connect_by_path(t160_name,'>'),
sys_connect_by_path(t158_folder_name,'>') from
(select t158_id, t158_file_name,t158_parent_f_category from category) c
join
(select t160_f_category, t160_name from category_locale where contains(t160_text,'%it% within t160_name')>0 ) l
on c.t158_id=l.t160_f_category
start with c.t158_parent_f_category is null
connect by prior t158_id=t158_parent_f_category;

Why it use normal index(on t160_f_category) rather than
use the text index ??
even if i use hint /*+ index ...*/
thanks,


Tom Kyte
December 01, 2006 - 5:17 am UTC

why didn't my car start?


You have as much chance of coming up with a good, complete answer for that as I do for this.

We have about the same amount of information.

YG, December 09, 2008 - 9:15 pm UTC

Hello,
I am trying to traverse tree in reverse direction and return full path (from leaf to root) with leaf's data in the same row.
Here's my test case:
create table t(id number,
               p_id number,
               name varchar2(10));
insert into t values(1,null,'root');
insert into t values(2,1,'a2');
insert into t values(3,1,'a3');
insert into t values(4,1,'a4');
insert into t values(5,2,'x1');
insert into t values(6,2,'x2');
insert into t values(7,3,'b3');
insert into t values(8,3,'b4');
insert into t values(9,6,'w3');
insert into t values(10,6,'w5');
insert into t values(11,7,'w3');
17:56:21 YG1> select * from t
17:56:59   2  /

          ID         P_ID NAME
------------ ------------ ------
           1              root
           2            1 a2
           3            1 a3
           4            1 a4
           5            2 x1
           6            2 x2
           7            3 b3
           8            3 b4
           9            6 w3
          10            6 w5
          11            7 w3
I can get full path:
SELECT LEVEL lvl,
       id,
       p_id,
       SYS_CONNECT_BY_PATH(name, '|') path_by_name
  FROM t
  START WITH name = 'w3'
  CONNECT BY id = PRIOR p_id
/
         LVL           ID         P_ID PATH_BY_NAME
------------ ------------ ------------ ------------------
           1            9            6 |w3
           2            6            2 |w3|x2
           3            2            1 |w3|x2|a2
           4            1              |w3|x2|a2|root
           1           11            7 |w3
           2            7            3 |w3|b3
           3            3            1 |w3|b3|a3
           4            1              |w3|b3|a3|root

It works fine but I need id from level 1 and path from level 4 in the same row.

This is the closeast I've got so far:
SELECT lvl,
       id,
       path_by_name 
  FROM(SELECT lvl,
              id,
              path_by_name,
              MIN(lvl) OVER() min_lvl,
              MAX(lvl) OVER() max_lvl
  FROM (SELECT LEVEL lvl,
               DECODE(LEVEL,1,id,NULL) id,
               SYS_CONNECT_BY_PATH(name, '|') path_by_name
          FROM t
         START WITH name = 'w3'
         CONNECT BY id = PRIOR p_id
       )
)
WHERE lvl = max_lvl
   OR lvl = min_lvl
/

         LVL           ID PATH_BY_NAME
------------ ------------ --------------------
           1            9 |w3
           4              |w3|x2|a2|root
           1           11 |w3
           4              |w3|b3|a3|root

What I need to get:
  ID PATH_BY_NAME
---- --------------------
   9 |w3|x2|a2|root
  11 |w3|b3|a3|root

Is it possible to do it in SQL, without PL/SQL?
Thank you

Tom Kyte
December 10, 2008 - 9:26 am UTC


ops$tkyte%ORA10GR2> select to_number(substr(scbp,1,10)) ID, max(substr(scbp,11)) path_by_name
  2    from (
  3  select substr( sys_connect_by_path(decode(level,1,to_char(id,'fm0000000000')) || name,'|'), 2 ) scbp
  4    from t
  5   start with name = 'w3'
  6   connect by prior p_id = id
  7         )
  8   group by to_number(substr(scbp,1,10))
  9   order by to_number(substr(scbp,1,10))
 10  /

        ID PATH_BY_NAME
---------- -------------------------
         9 w3|x2|a2|root
        11 w3|b3|a3|root

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select id, max(scbp) path_by_name
  2    from (
  3  select connect_by_root id id,
  4             sys_connect_by_path( name,'|') scbp
  5    from t
  6   start with name = 'w3'
  7   connect by prior p_id = id
  8         )
  9   group by id
 10   order by id
 11  /

        ID PATH_BY_NAME
---------- -------------------------
         9 |w3|x2|a2|root
        11 |w3|b3|a3|root

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select id, (select max( sys_connect_by_path(name,'|') )
  2                from t t2
  3                           start with t2.rowid = t1.rowid
  4                           connect by prior p_id = id) path_by_name
  5    from t t1
  6   where t1.name = 'w3'
  7   order by id
  8  /

        ID PATH_BY_NAME
---------- -------------------------
         9 |w3|x2|a2|root
        11 |w3|b3|a3|root





first one is 9i compatible, second two are 10g and above.

I liked the last one

Yelena Galuzo, December 22, 2008 - 6:07 pm UTC

Hello,
This version looks the most elegant:
select id, (select max( sys_connect_by_path(name,'|') )
from t t2
start with t2.rowid = t1.rowid
connect by prior p_id = id) path_by_name
from t t1
where t1.name = 'w3'
order by id
/
It works great in 9.2.0.8.0.
Thanks a lot,
YG

Tree Aggregation

A reader, October 19, 2009 - 5:46 pm UTC

How do I roll up numbers up a tree? Suppose I have the following tree (diagram #1). Each node is designated with an ID number,

representing a primay key in a table. Only leaf nodes have a number (preceded by the @ sign) associated with it (see

diagram #1). How do I write an efficient SQL that will add up numbers from the leaf nodes and roll up the numbers

up the entire tree all the way to the root node so the final tree looks like that depicted in diagram #2?


-----------------------
Diagram #1
-----------------------
                            (1)
                             |
                             |
                   ------------------------
                   |         |            |
                   |         |            |
                  (2)       (3)          (4)@10
                   |         |
                   |         |
              -------        |
              |     |        |
              |     |        |
             (5)   (6)@20   (7)@5
              |
              |
             (8)@30


-----------------------
Diagram #2
-----------------------
                            (1)@65
                             |
                             |
                   ------------------------
                   |         |            |
                   |         |            |
                  (2)@50    (3)@5        (4)@10
                   |         |
                   |         |
           ----------        |
           |        |        |
           |        |        |
          (5)@30   (6)@20   (7)@5
           |
           |
          (8)@30



CREATE TABLE tree (
  id       INTEGER PRIMARY KEY,
  parentID INTEGER REFERENCES tree(id),
  score    INTEGER
);

INSERT INTO tree VALUES (1, NULL, NULL);
INSERT INTO tree VALUES (2, 1, NULL);
INSERT INTO tree VALUES (3, 1, NULL);
INSERT INTO tree VALUES (4, 1, 10);
INSERT INTO tree VALUES (5, 2, NULL);
INSERT INTO tree VALUES (6, 2, 20);
INSERT INTO tree VALUES (7, 3, 5);
INSERT INTO tree VALUES (8, 5, 30);

COMMIT;


Tom Kyte
October 22, 2009 - 4:16 pm UTC


ops$tkyte%ORA10GR2> select rpad('*',2*level,'*') || id idstr, parentid, score,
  2         (select sum(score)
  3                from tree t2
  4                   start with t2.id = tree.id
  5                   connect by prior id = parentid) score2
  6    from tree
  7  start with parentid is null
  8  connect by prior id = parentid
  9  /

IDSTR                  PARENTID      SCORE     SCORE2
-------------------- ---------- ---------- ----------
**1                                                65
****2                         1                    50
******5                       2                    30
********8                     5         30         30
******6                       2         20         20
****3                         1                     5
******7                       3          5          5
****4                         1         10         10

8 rows selected.

Connect By ?

BC, October 27, 2009 - 9:39 am UTC

Is is possible to achieve the below using connect by ?

user@mydb > select * from visits;

   SITE_ID   VISIT_ID VISIT_DATE              VISIT_TYPE
---------- ---------- ----------------------- ----------
         1          1 28-Oct-2009 10:28:18 AM P
         1          2 29-Oct-2009 10:28:18 AM P
         1          3 30-Oct-2009 10:28:18 AM P
         1          4 31-Oct-2009 10:28:18 AM S
         1          5 01-Nov-2009 10:28:18 AM S
         1          6 02-Nov-2009 10:28:18 AM S
         1          7 03-Nov-2009 10:28:18 AM S
         1          8 04-Nov-2009 10:28:18 AM S
         1          9 05-Nov-2009 10:28:18 AM S
         1         10 06-Nov-2009 10:28:18 AM S

is it possible to select one visit_type = 'S' record with the least visit_date for every visit_type = 'P' record using a single sql statement ?

the view that I am working on, self joins the vist table 4 times to achieve the same ....., 

This is what is expected ...

   SITE_ID   VISIT_ID VISIT_DATE              VISIT_TYPE RELATED_VISIT_ID
---------- ---------- ----------------------- ---------- ----------------
         1          1 28-Oct-2009 10:28:18 AM P                         4
         1          2 29-Oct-2009 10:28:18 AM P                         4
         1          3 30-Oct-2009 10:28:18 AM P                         4
         
             
visits table is defined as

create table visits
(
   site_id     number(10),
   visit_id    number(10),
   visit_date  date,
   visit_type  varchar2(1)
);

test data can be inserted using the below

insert into visits
select   1,
         rownum,
         sysdate + rownum,
         case
            when rownum < 4 then
              'P'
            else
              'S'
         end
from     all_objects
where    rownum < 11;


Tom Kyte
October 27, 2009 - 12:05 pm UTC

... is it possible to select one visit_type = 'S' record with the least visit_date
for every visit_type = 'P' record using a single sql statement ?
...


why would connect by be relevant?

select *
from (select * from visits where visit_type='P'),
(select * from (select * from visits where visit_type='S' order by visit_date) where rownum = 1)
/


Awesome ....

BC, October 27, 2009 - 12:21 pm UTC

Tom,

Duh ..., I feel like such and idiot :)

Thank you so much for your time and the quick response.


hierarchical 11g r2

A reader, March 11, 2010 - 10:50 am UTC

Tom,

I saw this article;

http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it

Seems like it's a more "ansi-compliant" method of walking trees starting 11gR2, it looks more cumbersome than the "old" connect by. Wonder if you've seen/done any performance comparision between the "old" and "new" methods? I haven't got enough hardware capacity to load up 11gR2 to try, just wonder if you know about it already.
Tom Kyte
March 12, 2010 - 2:23 pm UTC

I have not, but I would anticipate (in fact, I know) different plans from the get-go since the recursive with subquery factoring can do things that connect by simply cannot. It is a different beast.


A reader, July 31, 2010 - 2:50 am UTC

Hi Tom,
If parent havig child(para_id-->prior child),the child record should come up with parent ,If not havig parent,is it sort the records based on can/pending status?.
[code]
par_id child x_status x_status1 date
100 null NEW pending 10/02/2009
102 null NEW pending 10/02/2009
101 100 UPD fixed 11/02/2009
200 102 upd fixed 11/02/2009
209 102 upd fixed 11/02/2009
201 xxx can pendig 10/02/2009
202 xxx can peding 10/02/2009
203 xxx upd fixed 11/02/2009
204 xxx upd fixed 11/02/2009

expecting o/p

par_id child x_status x_status1 date
100 null NEW pending 10/02/2009
101 100 UPD fixed 11/02/2009
102 null NEW pending 10/02/2009
200 102 upd fixed 11/02/2009
209 102 upd fixed 11/02/2009
201 xxx can pendig 10/02/2009
203 xxx upd fixed 11/02/2009
202 xxx can peding 10/02/2009
204 xxx upd fixed 11/02/2009
[/code]

Regards,
MR

Very helpful queries

sneha Dhanya, April 21, 2011 - 12:54 pm UTC

Hello Tom,

Can you please tell me a good book to refer for learning hierarchicahl queries syntaxes?
I really have a question but as it is mentioned not to ask new questions.. I am asking for a good book to refer.

Thanks in advance
Sneha
Tom Kyte
April 25, 2011 - 8:25 am UTC

The sql language reference teaches you the syntax,

time, practice, application of the technique

is necessary to learn HOW to use it. I doubt you need help with the syntax - that is very cut and dry. You need time spent using the syntax to "learn" how it works.

Just like you needed time with FOR when learning to program and such.

A reader, July 26, 2011 - 2:05 pm UTC

Tom,

I need to finish data merging for half a million of records, which, I think, could be done by "connect by". For instance,
prm_id dup_id
1 2
2 3
2 4
3 4
10 11
11 12

Final result need to be like this:
prm_id dup_id
1 2
1 3
1 4
10 11
10 12

That means that 2, 3, 4 will be merged to 1, and 11 and 12 will be merged to 10. To find the merging list, I used the following query:

select distinct prm_id, dup_id
from match_list
where connect_by_isleaf =1
connect by prior prm_id = dup_id;

I tried a few records and the results seem fine. However, when running it on the half a million records, it takes forever. Any suggestion on it? Thanks.
Tom Kyte
July 28, 2011 - 6:47 pm UTC

define "forever", describe the machine this is run on, share the plan.

doesn't seem like it should take more than a few seconds - given that it is such a tiny table, just a 1/2 million records.

for example, on my laptop....


ops$tkyte%ORA11GR2> create table t ( prm_id number, dup_id number );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 1, 2 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 2, 3 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 2, 4 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 3, 4 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 10, 11 );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 11, 12 );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select distinct prm_id, dup_id
  2  from t
  3  where connect_by_isleaf =1
  4  connect by prior prm_id = dup_id;

    PRM_ID     DUP_ID
---------- ----------
         1          2
        10         11

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t
  2  with data as (select 100 + 100*level l from dual connect by level <= 100000)
  3  select prm_id+l, dup_id+l from t, data
  4  /

600000 rows created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set timing on
ops$tkyte%ORA11GR2> set autotrace traceonly statistics
ops$tkyte%ORA11GR2> select distinct prm_id, dup_id
  2  from t
  3  where connect_by_isleaf =1
  4  connect by prior prm_id = dup_id;

200002 rows selected.

Elapsed: 00:00:14.78

Statistics
----------------------------------------------------------
          6  recursive calls
          1  db block gets
       1455  consistent gets
        525  physical reads
        176  redo size
    4293486  bytes sent via SQL*Net to client
     147083  bytes received via SQL*Net from client
      13335  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     200002  rows processed

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> select count(*) from t;

  COUNT(*)
----------
    600006

Elapsed: 00:00:00.04
ops$tkyte%ORA11GR2> set timing off

A reader, August 01, 2011 - 9:11 am UTC

Many thanks, Tom.

A reader, August 15, 2011 - 8:38 am UTC

In case that someone else has the similar problem in the future. Our data contains 54 records which match each other. That is the bottleneck and make the query choked. Fortunately, 54 records are "test" data. After deleting them, the query results of ~500,000 records came out quickly. Thanks, Tom and see you at OOW in Oct.

Hierarchical Query with time dimention

Poorni, November 12, 2014 - 8:41 pm UTC

Hi Tom ,

We have a table something like below which captures the change of hierarchy over time.

Employee Manager fromdate todate

Scott king 2010 Null
King smith 2010 Null
Smith Adam 2010 2011
Smith Fox 2012 Null
Adam Fox 2009 Null
Fox Null 2009 Null

and need an output like

fox 2009 null
Fox :Adam 2009 null
Fox :Adam :Smith 2010 2011
Fox :Adam :Smith:king 2010 2011
Fox :Adam:Smith:King:Scott 2010 2011
Fox :Smith 2012 null
Fox :Smith : king 2012 null
Fox :Smith:King:Scott 2012 null

When I use the connect by approach , it ends up with Cartesian product.Thanks in advance. Appreciate the help.

Remove the lowest node from the result

Lovlesh, September 21, 2017 - 11:05 am UTC

Hi,

i am using the below query to get the parent hierarchy,

SELECT LISTAGG (child, ' - ')
WITHIN GROUP (ORDER BY LEVEL DESC)
FROM dummy_table pt1
START WITH child = 5
CONNECT BY PRIOR parent = child

i get the result as '1 - 2 -3 -4 - 5'.
how can i remove the '5' from the above result i.e. the lowest node from the end result
Connor McDonald
September 22, 2017 - 12:46 am UTC

SQL> create table t ( p int, c int );

Table created.

SQL>
SQL> insert into t values (4,5);

1 row created.

SQL> insert into t values (3,4);

1 row created.

SQL> insert into t values (2,3);

1 row created.

SQL> insert into t values (1,2);

1 row created.

SQL> insert into t values (null,1);

1 row created.

SQL>
SQL> select listagg (
  2              case when c != connect_by_root c then c end,
  3          ' - ') within group (order by level desc)
  4  from t
  5  start with c = 5
  6  connect by prior p = c ;

LISTAGG(CASEWHENC!=CONNECT_BY_ROOTCTHENCEND,'-')WITHINGROUP(ORDERBYLEVELDESC)
------------------------------------------------------------------------------------
1 - 2 - 3 - 4


Jitendra, February 22, 2018 - 7:31 pm UTC

Hello,

I am trying to search a child in tree and return all parent nodes (from leaf to root).
Here's my test case:

create table t(id number,
p_id number,
name varchar2(10), display_order number);
insert into t values(1,1,'root',1);
insert into t values(2,1,'a2',1);
insert into t values(3,1,'a3',2);
insert into t values(4,1,'a4',3);
insert into t values(5,2,'x1',1);
insert into t values(6,2,'x2',2);
insert into t values(7,3,'b3',1);
insert into t values(8,3,'b4',2);
insert into t values(9,6,'w3',1);
insert into t values(10,6,'w5',2);
insert into t values(11,7,'w3',1);

This is the closeast I've got so far:
SELECT p_id, id, name, display_order
FROM t
START WITH name like 'w%'
CONNECT BY nocycle id = PRIOR p_id
order by rownum desc

-----------------------
Table #1
-----------------------

P_ID ID Name display_order
------------ ------------ ------------ ------------------
1 3 a3 2
3 7 b3 1
7 11 w3 1
1 2 a2 1
2 6 x2 2
6 10 w5 2
1 2 a2 1
2 6 x2 2
6 9 w3 1

But i need the rows in following order

-----------------------
Table #2
-----------------------

P_ID ID Name display_order
------------ ------------ ------------ ------------------
1 1 root 1
1 2 a2 1
2 6 x2 2
6 9 w3 1
6 10 w5 2
1 3 a3 2
3 7 b3 1
7 11 w3 1

-----------------------
Diagram #1
-----------------------

root
|__a2
| |__x2
| |__w3
| |__w5
|
|__a3
|__b3
|__w3


Is it possible to get the rows as in the Table #2
*sibling should be in the order of display order

Connor McDonald
February 23, 2018 - 1:54 am UTC

SQL> with
  2    t_fixed as
  3       ( select id, decode(name,'root',null,p_id) p_id, name, display_order from t ),
  4    nodes_of_interest as
  5       ( select distinct id,p_id,name,display_order
  6          from t_fixed
  7          start with name like 'w%'
  8          connect by prior p_id =  id
  9       )
 10  select *
 11  from   nodes_of_interest
 12  start with p_id is null
 13  connect by p_id = prior id
 14  order siblings by display_order;

        ID P_ID                                     NAME       DISPLAY_ORDER
---------- ---------------------------------------- ---------- -------------
         1                                          root                   1
         2 1                                        a2                     1
         6 2                                        x2                     2
         9 6                                        w3                     1
        10 6                                        w5                     2
         3 1                                        a3                     2
         7 3                                        b3                     1
        11 7                                        w3                     1

8 rows selected.