3

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 ?

Rodriguez
  • 452
  • 1
  • 5
  • 16

2 Answers2

2

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
Lennart - Slava Ukraini
  • 6,936
  • 1
  • 20
  • 32
2

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
Community
  • 1
  • 1
Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78