CODEWITHSUNDEEP
sqloracle-databaseconnect-by
Rodriguez
Rodriguez

Reputation: 452

Oracle Connect By top/bottom hierarchy

How can I get the top and bottom of a hierarchy using connect by, I have a table that stores transition between ids(ID-> REPLACE_ID), I'm interested in getting the latest 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 the transitions from 1-4 and 51-54, alternatively I can start with 2 and get 2-4. Is there anything I can group by to identify group that start with 1 and the group that start with 51 ?

Upvotes: 3

Views: 1952

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27261

As one(more straightforward) of the approaches you can simply find lowest id and highest replace_id grouping by connect_by_root() value, if replace_id is always guaranteed to be greater than id, otherwise see Lennart 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

Upvotes: 2

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

Untested, so there might be some errors in there:

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

Upvotes: 2

Related Questions