Find ancestor from hierarchy

I needed to find a quick way to select a particular ancestor for a child in a hierarchical table. For example: “Find the manager of SMITH” (parent), or “Find SMITH’s manager’s manager” (grand parent).

Using the emp table as an example, I came up with the following solution:

SELECT sys_connect_by_path(ename, ':') path,
       ltrim(regexp_substr(
                sys_connect_by_path(ename, ':'), 
                ':[A-Z]{1,}', 1, &x),':'
       ) ancestor
  FROM emp
 WHERE connect_by_isleaf = 1
 START WITH ename = 'SMITH'
CONNECT BY PRIOR mgr = empno
 ORDER SIBLINGS BY ename;

This query traverses the hierarchical tree up from the child, and limits it to only that row where the employee I’m looking for is the leaf (connect_by_isleaf=1), i.e. the lowest in the hierarchy.  Then I use the sys_connect_by_path to fetch the entire hierarchical path, and utilize the regexp_substr function to pick out the ancestor. Which ancestor is selected depends on &x. If x=1 then the child itself is selected. If x=2, the parent is selected; x=3 is the grand parent and so on.

SQL > /
Enter value for x: 3
PATH                           ANCESTOR
------------------------------ ----------
:SMITH:FORD:JONES:KING         JONES
SQL >

Lastly, here are a couple of examples that show traversing the path forwards and backwards from/to a given descendant:

col ename for a20
col root for a20
col path for a30
PROMPT Forwards path:
PROMPT ==============
SELECT connect_by_root ename root
     , sys_connect_by_path(ename, ':') path
     , ename
  FROM emp
 WHERE ename          = 'SMITH'
START WITH ename       = 'KING'
CONNECT BY prior empno = mgr
/


PROMPT Backwards path:
PROMPT ===============
SELECT connect_by_root ename ename
     , sys_connect_by_path(ename, ':') path
     , ename root
  FROM emp
 WHERE connect_by_isleaf = 1
 START WITH ename       = 'SMITH'
CONNECT BY empno = prior mgr
/

Forwards path:
==============

ROOT                 PATH                           ENAME
-------------------- ------------------------------ --------------------
KING                 :KING:JONES:FORD:SMITH         SMITH

Backwards path:
===============

ENAME                PATH                           ROOT
-------------------- ------------------------------ --------------------
SMITH                :SMITH:FORD:JONES:KING         KING