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:

[sourcecode language=”sql”]
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;
[/sourcecode]

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.

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

4 thoughts on “Find ancestor from hierarchy

  • August 17, 2015 at 2:26 pm
    Permalink

    Christopher,
    This is absolutely the best explanation and illustration of these hierarchical functions that I’ve run across! Thanks so much.
    We have Laserfiche as our document management system and its core table is hierarchical. Navigating the trees has been an awful challenge. We have about 1 million documents and sifting through the metadata just got a lot easier with your examples.
    Thanks again.
    Jack

    Reply
  • March 4, 2018 at 2:18 am
    Permalink

    This is great thanks alot

    Reply

Leave a Reply

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