Reputation: 416
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
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