couatl
couatl

Reputation: 416

Returnable CONNECT BY PRIOR (SQL)

I have a structure (example, in brackets (id, parent_id) ):

In input I have list of id If i don't have id parent directory, but I have child id, then I must show all ancestry: For example: Input: 83, 46, 43, 48 Output:

I invented request:

SELECT * 
  FROM (SELECT distinct * 
          FROM Table a 
          START WITH N in (83, 46, 43, 48) CONNECT BY PRIOR N_PARENT = N) a 
          START WITH N_PARENT in 0 CONNECT BY PRIOR N = N_PARENT
         ORDER SIBLINGS BY N

But in the big data is slow. Can I make request with one CONNECT BY?

Upvotes: 0

Views: 281

Answers (1)

Kirill Leontev
Kirill Leontev

Reputation: 10941

with tree as ( 
 SELECT t.name, t.id, t.parent, LEVEL lvl, connect_by_root(id) root
   FROM t
CONNECT BY PRIOR parent = id
  START WITH id IN (83, 98, 46, 43, 48)
), ordered_tree as (
 select name, 
        id, 
        parent,
        row_number() over (partition by id order by id) rn
   from tree 
  order by root, lvl desc
)
select name, id, parent
 from ordered_tree where rn = 1 

http://sqlfiddle.com/#!4/34f2d/4

Upvotes: 2

Related Questions