How to get the root record in Oracle database

Question

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?

Am I reading this right? There are two roots, and the `msg_id` for both is just `'3'`? — , Apr 23 '20 at 17:16

GMB · Answer 1 · 2020-04-23T17:28:12.707

0

You can use a recursive query:

with cte (id, msg_id, parent_id) as (
    select id, msg_id, parent_id, from mytable where id = ?
    union all
    select t.id, t.msg_id, t.parent_id
    from mytable t
    inner join cte c on c.parent_id = t.id
)
select * from cte where parent_id is null

edited Apr 23 '20 at 17:28

answered Apr 23 '20 at 16:06

GMB

216,147
25
84
135

There is no need to scan `cte` a second time - and there is no need for `lvl`. The `where` clause should be `where parent_id is null`. – Apr 23 '20 at 17:27

score 0 · Accepted Answer · answered Apr 23 '20 at 17:23

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
;

How to get the root record in Oracle database

2 Answers2