Get Root parent of child in Hierarchical table

Question

I have a table with hierarchical data in it, the structure goes like this:

ID      ParentId
----   ----------
1       NULL
2       1
3       2
4       2
5       3
6       5

If I pass the node Id I would like to get the top most node Id/details by traversing through all its parents in SQL.

I tried CTE, i somehow cannot get the combination correct. However, i got this working as a function but it is so slow that i had to post this question.

In the above example if I pass 6, i would want to have the top most i.e. 1. By traversing through 6 => 5 => 3 => 2 => [1] (result)

Thanks in advance for your help.

CTE is Common Table Expression. – Immortal Jul 02 '14 at 05:42 — Immortal, Jul 02 '14 at 05:42

score 10 · Answer 1 · answered Oct 12 '16 at 00:01

10

DECLARE @id INT = 6
;WITH parent AS
(
    SELECT id, parentId, 1 AS [level] from tbl WHERE id = @id
    UNION ALL 
    SELECT t.id, t.parentId, [level] + 1 FROM parent
    INNER JOIN tbl t ON t.id =  parent.parentid
)
SELECT TOP 1 id FROM parent ORDER BY [level] DESC

@TechDo's answer assumes the lowest ID will be the parent. If you don't want to rely on this then the above query will sort by the depth.

answered Oct 12 '16 at 00:01

Douglas Marttinen

396
4
10

This should be the accepted answer. TechDos answer will give the wrong result if the root has higher id (which is a common case) – Mathias F Oct 25 '19 at 09:55
Is there a way to extend this to get the root parent id of each id – Avin Kavish Jan 23 '21 at 08:54
@AvinKavish if I'm understanding correctly, I think you can create a function with the above query, then use the function in your own query to get the root ID of each ID. – Douglas Marttinen Jan 29 '21 at 03:10

score 7 · Accepted Answer · answered Jul 02 '14 at 05:43

7

Please try:

declare @id int=6
;WITH parent AS
(
    SELECT id, parentId  from tbl WHERE id = @id
    UNION ALL 
    SELECT t.id, t.parentId FROM parent
    INNER JOIN tbl t ON t.id =  parent.parentid
)

SELECT TOP 1 id FROM  parent
order by id asc

answered Jul 02 '14 at 05:43

TechDo

18,398
3
51
64

1

doesnt this assume that the root id is the lowest id of the tree? What if the root id has a higher id then the oder nodes? – Mathias F Oct 25 '19 at 09:49

score 2 · Answer 3 · answered Jul 02 '14 at 05:52

You can try this query my friend to get all ids:

with tab1(ID,Parent_ID) as
(select * from table1 where id = 6
union all
select t1.* from table1 t1,tab1 
where tab1.Parent_ID = t1.ID)
select ID from tab1;

and this query will give the final result:

with tab1(ID,Parent_ID) as
(select * from table1 where id = 6
union all
select t1.* from table1 t1,tab1 
where tab1.Parent_ID = t1.ID)
select ID from tab1 where parent_id is null;

SQL Fiddle

score 2 · Answer 4 · edited May 05 '21 at 10:08

 WITH CTE_MyTable AS (
    SELECT        Id, ParentId, NULL As RootParent, 1 As Lvl
    FROM            dbo.MyTable
    UNION ALL
    SELECT        a.id, b.ParentId, a.ParentId As RootParent, Lvl + 1
    FROM            CTE_MyTable a INNER JOIN
                                               dbo.MyTable b ON a.ParentId = b.Id
)
, CTE_MyTable_RN AS  (
    SELECT Id, RootParent, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Lvl DESC) RN
    FROM CTE_MyTable
)

SELECT Id, ISNULL(RootParent, Id) As RootParent
FROM CTE_MyTable_RN
WHERE RN = 1

score 1 · Answer 5 · answered Jul 02 '14 at 05:48

;WITH CTE
as
(
    Select I.ID,P.Parent_id
    from #temp I 
    join #temp P 
    on P.Id = I.Parent_Id
    where i.ID = 6
    union all
    Select I.ID,P.Parent_id
    from CTE I 
    join #temp P 
    on P.Id = I.Parent_Id
    where p.Parent_Id is not null
)
Select ID,min(parent_id) from CTE group by id;

Get Root parent of child in Hierarchical table

5 Answers5

SQL Fiddle

Linked