CODEWITHSUNDEEP
sqloracle-databaseself-referencing-table
Nik Kashi
Nik Kashi

Reputation: 4606

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
-------------------------------------------------

Upvotes: 0

Views: 138

Answers (1)

Nik Kashi
Nik Kashi

Reputation: 4606

this works for me.

SELECT m.*,
  CONNECT_BY_ROOT id AS second_level_parent_id
FROM my_table m
WHERE CONNECT_BY_ROOT level =2
  CONNECT BY prior id    = parent_id;

thanks @Jozef Dúc

Upvotes: 1

Related Questions