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.

Tuberose

CTE is Common Table Expression. – Tuberose

Answer 1

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.

Salable

This should be the accepted answer. TechDos answer will give the wrong result if the root has higher id (which is a common case) – Surcharge

Is there a way to extend this to get the root parent id of each id – Steelworks

@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. – Salable

Answer 2

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

Brownnose

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? – Surcharge

Answer 3

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

Flofloat

Answer 4

 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

Zeiler

Answer 5

;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;

Hello