Oracle: Recursively self referential join with nth level record 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
-------------------------------------------------
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