Hierarchical Query: Get the ancestors in top down order

This query starts with the leaf node and climbs up the hierarchy to the top ancestor. It then displays the result in the usual top-down order:

[sourcecode language=”sql”]

CMR@vrep > list
select lvl
,employee
from (select level lvl
,lpad(‘ ‘, 2 * (
max(rownum)
over () – level)
, ‘ ‘)
||empno
||’, ‘
||ename employee
,rownum rn
from emp
connect by empno=prior mgr
start with ename=upper(‘&ename’))
order by rn desc

CMR@vrep > /
Enter value for ename: adams

LVL EMPLOYEE
———- ————————–
4 7839, KING
3 7566, JONES
2 7788, SCOTT
1 7876, ADAMS

CMR@vrep >

[/sourcecode]

 

Leave a Reply

Your email address will not be published. Required fields are marked *