How do I find the nearest common ancestor node in a SQL Statement?

The Oracle (tm) Users' Co-Operative FAQ

How do I find the nearest common ancestor node (Hierarchy Tree) in a SQL Statement?

Back to index of questions

The following SQL query pattern can be used to find the nearest common ancestor node

in the Hierarchy Tree.

create table emp1 ( empno number, ename varchar2(10), mgr number );

insert into emp1 ( empno, ename ) values ( 1, 'BIG_BOSS' );

insert into emp1 ( empno, ename , mgr) values ( 7839, 'KING', 1 );

insert into emp1 ( empno, ename , mgr) values ( 7566, 'JONES', 7839 );

insert into emp1 ( empno, ename , mgr) values ( 7788, 'SCOTT', 7566 );

insert into emp1 ( empno, ename , mgr) values ( 7876, 'ADAMS', 7788 );

insert into emp1 ( empno, ename , mgr) values ( 7902, 'FORD', 7566 );

insert into emp1 ( empno, ename , mgr) values ( 7369, 'SMITH', 7902);

insert into emp1 ( empno, ename , mgr) values ( 7698, 'BLAKE', 7839 );

insert into emp1 ( empno, ename , mgr) values ( 7499, 'ALLEN', 7698 );

insert into emp1 ( empno, ename , mgr) values ( 7521, 'WARD', 7698 );

insert into emp1 ( empno, ename , mgr) values ( 7654, 'MARTIN', 7698 );

insert into emp1 ( empno, ename , mgr) values ( 7844, 'TURNER', 7698 );

insert into emp1 ( empno, ename , mgr) values ( 7900, 'JAMES', 7698 );

insert into emp1 ( empno, ename , mgr) values ( 7782, 'CLARK', 7839 );

insert into emp1 ( empno, ename , mgr) values ( 7934, 'MILLER', 7782 );

column info format a55

select rpad('*',2*level,'*') || empno || '.' || ename info

from emp1

start with mgr is null

connect by prior empno = mgr;

INFO                                                                            

------------------------                            

**1.BIG_BOSS                                                                   

****7839.KING                                                                   

******7566.JONES                                                               

********7788.SCOTT                                                             

**********7876.ADAMS                                                            

********7902.FORD                                                              

**********7369.SMITH                                                           

******7698.BLAKE                                                               

********7499.ALLEN                                                             

********7521.WARD                                                              

********7654.MARTIN                                                                                          

********7844.TURNER                                                            

********7900.JAMES                                                             

******7782.CLARK                                                               

********7934.MILLER                                                            

15 rows selected.

SQL> variable input varchar2(28)

SQL> exec :input := '7369,7934'

SELECT ename, empno, lev, cnt

FROM

(

  WITH INPUT_DATA AS (

  SELECT SUBSTR(str,

                INSTR(str, ',', 1, LEVEL  ) + 1,

                INSTR(str, ',', 1, LEVEL+1) -

                INSTR(str, ',', 1, LEVEL) -1 ) str      

FROM ( SELECT ','||:input||',' AS str  FROM DUAL )

CONNECT BY PRIOR STR = STR

AND INSTR (str, ',', 1, LEVEL+1) > 0

AND PRIOR dbms_random.string ('p', 10) IS NOT NULL )

SELECT DISTINCT ename, empno, lev, cnt

 FROM

 (SELECT ename, empno, mgr,

         MIN(level) OVER ( PARTITION BY ename) lev,

         COUNT(*) OVER ( PARTITION BY ename) cnt

   FROM  emp1

   START WITH empno IN

   (SELECT CASE WHEN UPPER(str) = 'NULL' THEN NULL

                ELSE str END

       FROM INPUT_DATA)

      CONNECT BY PRIOR mgr = empno

   )

   WHERE cnt = (SELECT count(DISTINCT str) FROM INPUT_DATA )

   ORDER BY lev

 )

 WHERE ROWNUM = 1

ENAME           EMPNO        LEV        CNT                                    

---------- ---------- ---------- ----------                                    

KING             7839          3          2                                    

SQL> spool off

Back to top

Back to index of questions