How to start from the root and traverse down for a table which has current and next ID columns

Table structure

Current_id	Next_id
901	902
902	903
904	901

Want to detect the root and start from there - in the above example, the root is 904 and so the output has to

I understand if I start using parent_id, current_id structure and had (0,904) for (parent, current_id), I could use "start with" parent_id=0 to traverse the chain. But my table is about current and child. If it helps in coming up with a solution, I am happy to have the next_id for the last id row in the chain to be some very large fixed value (i.e 903, 10000000000)

How can I detect and start from the root node, when I have current and next instead of the parent and child columns.

I am ok even if I can get a SQL to get just the root - i.e. 904 in the above case. Any suggestions?

A simple "connect by Current_id=Next_id" didn't help as it doesn't determine the root without start with and I don't have a fixed start with.

Solution

If you want the values as a delimited list then you can use a hierarchical query and SYS_CONNECT_BY_PATH:

SELECT CONNECT_BY_ROOT current_id
       || SYS_CONNECT_BY_PATH(next_id, ',') AS path
FROM   table_name t
WHERE  CONNECT_BY_ISLEAF = 1
START WITH current_id NOT IN (SELECT next_id FROM table_name)
CONNECT BY PRIOR next_id = current_id

Which, for the sample data:

CREATE TABLE table_name (Current_id, Next_id) AS
SELECT 901, 902 FROM DUAL UNION ALL
SELECT 902, 903 FROM DUAL UNION ALL
SELECT 904, 901 FROM DUAL;

Outputs:

PATH
904,901,902,903

If you want the values as rows, you can find them using a hierarchical query:

SELECT t.*,
       LEVEL As depth,
       CONNECT_BY_ISLEAF AS leaf
FROM   table_name t
START WITH current_id NOT IN (SELECT next_id FROM table_name)
CONNECT BY PRIOR next_id = current_id

Which outputs:

CURRENT_ID	NEXT_ID	DEPTH	LEAF
904	901	1	0
901	902	2	0
902	903	3	1

If you just want the id values in a single column without duplicates then you can UNPIVOT and filter out the duplicates:

SELECT id
FROM   (
         SELECT t.*,
                LEVEL AS depth,
                CONNECT_BY_ISLEAF AS leaf
         FROM   table_name t
         START WITH current_id NOT IN (SELECT next_id FROM table_name)
         CONNECT BY PRIOR next_id = current_id
       )
       UNPIVOT (
         id FOR type IN (current_id AS 1, next_id AS 2)
       )
WHERE  type = 1 OR leaf = 1

Which outputs:

ID
904
901
902
903

fiddle