Starting from the generic node (or leaf) how to get the root node?
SELECT ID,MSG_ID,PARENT_ID FROM TABLE_X CONNECT BY PRIOR PARENT_ID = ID; ID MSG_ID PARENT_ID 4 3 NULL 5 93bea0f71b07-4037-9009-f148fa39bb62 4 4 3 NULL 6 6f5f5d4ab1ec-4f00-8448-7a6dfa6461b2 4 4 3 NULL 7 3 NULL 8 7e0fae569637-4d29-9075-c273eb39ae8e 7 7 3 NULL 9 8a3e7485b3e8-45b1-a31d-c52fd32111c0 7 7 3 NULL 10 fcc622d5af92-4e61-8d7c-add3da359a8b 7 7 3 NULL
How to get the root msg_id?
Advertisement
Answer
You were on the right path, but you are missing two essential ingredients.
First, to indicate the starting point, you need to use the start with
clause. Obviously, it will be something like start with id = <input value>
. You didn’t tell us how you will provide the input value. The most common approach is to use bind variables (best for many reasons); I named the bind variable input_id
in the query below.
Second, you only want the “last” row (since you are navigating the tree in the opposite direction: towards the root, not from the root; so the root is now the “leaf” as you navigate this way). For that you can use the connect_by_isleaf
pseudocolumn in the where
clause.
So, the query should look like this: (note that I am only selecting the root message id, as that is all you requested; if you need more columns, include them in select
)
select msg_id from table_x where connect_by_isleaf = 1 -- keep just the root row (leaf in this traversal) start with id = :input_id -- to give the starting node connect by prior parent_id = id ;