0

I have self referential table like this:

id   |level | parent_id
----------------------
1    |1     |null
2    |1     |null
3    |2     |1
4    |2     |1
5    |2     |2
6    |3     |5
7    |3     |3
8    |4     |7
9    |4     |6
------------------------

I need nth level parent in result. for example 2nd level parent

id   |level | parent_id| second_level_parent_id
------------------------------------------------
1    |1     |null      |null
2    |1     |null      |null
3    |2     |1         |null
4    |2     |1         |null
5    |2     |2         |null
6    |3     |5         |5
7    |3     |3         |3
8    |4     |7         |3
9    |4     |6         |5
-------------------------------------------------

1 Answer 1

1

You can use the SYS_CONNECT_BY_PATH hierarchical function to generate a list of parent IDs from the tree root to the current record, and the level pseudo column to count the number of members in the list (+1). In the next stage, the regexp_substr function extracts the Nth path element from the ath for display as the Nth parent_Id:

var nth number
exec :nth := 2;

with t1(id, lev, parent_id) as (
select 1, 1, null from dual union all
select 2, 1, null from dual union all
select 3, 2, 1    from dual union all
select 4, 2, 1    from dual union all
select 5, 2, 2    from dual union all
select 6, 3, 5    from dual union all
select 7, 3, 3    from dual union all
select 8, 4, 7    from dual union all
select 9, 4, 6    from dual
), t2 as (
select id, lev, parent_id, level
     , ltrim(SYS_CONNECT_BY_PATH(parent_id,','),',') path
  from t1
connect by prior id = parent_id
start with parent_id is null
)
select id, lev, parent_id, path
     , :nth n
     , to_number(regexp_substr(path,'[^,]+',1,:nth)) nth
  from t2
  order by id;

        ID        LEV  PARENT_ID PATH                N        NTH
---------- ---------- ---------- ---------- ---------- ----------
         1          1                                2           
         2          1                                2           
         3          2          1 1                   2           
         4          2          1 1                   2           
         5          2          2 2                   2           
         6          3          5 2,5                 2          5
         7          3          3 1,3                 2          3
         8          4          7 1,3,7               2          3
         9          4          6 2,5,6               2          5

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.