Oracle Connect Top / Bottom Hierarchy
How can I get the top and bottom of the hierarchy using connect by, I have a table that stores the transition between IDs (ID-> REPLACE_ID), I'm interested in getting the last ID starting from any id.
--drop table test_connect_by;
create table test_connect_by(ID number, REPLACE_ID NUMBER);
insert into test_connect_by values(1,2);
insert into test_connect_by values(2,3);
insert into test_connect_by values(3,4);
insert into test_connect_by values(51,52);
insert into test_connect_by values(52,53);
insert into test_connect_by values(53,54);
insert into test_connect_by values(55,55);
SELECT id,replace_id, level
FROM test_connect_by
START WITH ID in (1,51)
CONNECT BY PRIOR replace_id = id;
I'm interested in getting transitions from 1-4 and 51-54, otherwise I can start from 2 and get 2-4. Is there anything I can group to define a group that starts with 1 and a group that starts with 51?
source to share
As one (simpler) approach, you can simply find the lowest id
and highest replace_id
grouping by value connect_by_root()
if replace_id
there is always more than id
, otherwise see Lennart's answer :
select min(id) as begins
, max(replace_id) as ends
from test_connect_by
start with id in (1, 51)
connect by id = prior replace_id
group by connect_by_root(id)
Result:
BEGINS ENDS
---------- ----------
1 4
51 54
source to share
Unconfirmed, so there might be some errors:
select id, replace_id
from (
SELECT CONNECT_BY_ROOT id as id, replace_id
, row_number() over (partition by CONNECT_BY_ROOT id order by level desc) as rn
FROM test_connect_by
START WITH ID in (1,51)
CONNECT BY PRIOR replace_id = id
) as T
where rn = 1
source to share