Get Root parent of child in Hierarchical table
Solution 1
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
Solution 2
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.
Solution 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
Solution 4
;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;
Solution 5
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
Immortal
A Seasoned full stack engineer with advanced expertise of Microsoft Technologies, Database, Angular
Updated on July 29, 2022Comments
-
Immortal almost 2 years
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.
-
Mathias F over 4 yearsdoesnt 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 over 4 yearsThis should be the accepted answer. TechDos answer will give the wrong result if the root has higher id (which is a common case)
-
Avin Kavish over 3 yearsIs there a way to extend this to get the root parent id of each id
-
Douglas Marttinen over 3 years@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.