Good time of day! There is some table tb:

ID | PARENT_ID | LEV --------------------------- 10165 |10167 |0 10167 |10163 |0 10163 |10000 |2 10000 |null |2

The task is for all IDs to find the nearest PARENT_ID with LEV = 2. Nesting level can be any. For example, for ID = 10165 it will be 10163. The value for one id can be found by recursive query:

 select ID from tb where LEV='2' start with ID='10165' connect by ID = prior PARENT_ID and rownum=1;

But you need to find a single query for all IDs and display them in a new table: ID | PARENT_LEV2_ID Tell me how to do this?

  • Read about stored procedures. - Aleksei Chibisov
  • And for ID 10163 which is the closest one with lev = 2 or 10,000? - Mike
  • Mike, for id = 10163 the closest is himself. - Kim Belov

1 answer 1

We go on the other hand, from all records with lev = 2 we go to all children. the nesting level of connect by ( level ) is the distance from a given root to a specific child element. We need to take the path along which the path is minimal for each child element. We number the records by level and take the first ones.

 select ID, root from ( select ID,connect_by_root(id) root, row_number() over(partition by id order by level) rn from tb start with lev=2 connect by PRIOR ID = PARENT_ID ) where rn=1