Oracle SQL recursive query to find parent of level one

Question

suppose i have a table service :

     Name      | ID | PARENT_ID | LEVEL | 
------------------------------------------- 
 s1            | 1  | null      | 0     | 
 s2            | 2  | 1         | 1     | 
 s3            | 3  | 1         | 2     |      
 s4            | 4  | 2         | 2     | 
 s5            | 5  | 3         | 3     | 
 s6            | 6  | 4         | 3     |

and i want to get the parent of level 1 for s6(id=6) which should return s2 , is there a way to make a recursive query until a level is reached ?

@danisius difference here is that i have a level column which can be any number and the query has to go up until the specified level is reached — Mouna Cheikhna
– Mouna Cheikhna, Commented Sep 11, 2013 at 13:24

Kirill Leontev · Accepted Answer · 2013-09-11 13:32:50Z

You can go UP the tree instead of going down - from leaf (id = 6) to root (which in this reverse case itself would be a leaf, connect_by_isleaf = 1), and take a "parent" of that leaf using prior operator.

upd: Misunderstood your requirement about LEVEL (in Oracle hierarchical queries it is a dynamic pseudocolumn specifying hierarchical depth of a row). If you want to limit your result set to rows with a specific value of your custom pre-populated LEVEL column - you can just add it to where condition.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE t
    ("NAME" varchar2(2), "ID" int, "PARENT_ID" int, "LVL" int)
;

INSERT ALL 
    INTO t ("NAME", "ID", "PARENT_ID", "LVL")
         VALUES ('s1', 1, NULL, 0)
    INTO t ("NAME", "ID", "PARENT_ID", "LVL")
         VALUES ('s2', 2, 1, 1)
    INTO t ("NAME", "ID", "PARENT_ID", "LVL")
         VALUES ('s3', 3, 1, 2)
    INTO t ("NAME", "ID", "PARENT_ID", "LVL")
         VALUES ('s4', 4, 2, 2)
    INTO t ("NAME", "ID", "PARENT_ID", "LVL")
         VALUES ('s5', 5, 3, 3)
    INTO t ("NAME", "ID", "PARENT_ID", "LVL")
         VALUES ('s6', 6, 4, 3)
SELECT * FROM dual
;

Query 1:

select id as id, name as name from t
where lvl = 1
connect by  id = prior parent_id
  start with id = 6

Results:

| ID | NAME |
|----|------|
|  2 |   s2 |

René Nyffenegger · Accepted Answer · 2013-09-11 13:16:02Z

3

This is possible with a hierarchical query:

create table tq84_h (
  id number,
  parent_id number,
  level_    number
);

insert into tq84_h values (1, null, 0);
insert into tq84_h values (2, 1   , 1);
insert into tq84_h values (3, 1   , 2);
insert into tq84_h values (4, 2   , 2);
insert into tq84_h values (5, 3   , 3);
insert into tq84_h values (6, 4   , 3);

select 
  parent_id
from
  tq84_h
where
  level_ = 2
start with
  id = 6
connect by
  prior parent_id = id and
  level_>1
;

answered Sep 11, 2013 at 13:16

René Nyffenegger

40.9k35 gold badges180 silver badges321 bronze badges

Collectives™ on Stack Overflow

Oracle SQL recursive query to find parent of level one

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related