Immortal Immortal - 15 days ago 7
SQL Question

Get Root parent of child in Hierarchical table

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

ID ParentId
---- ----------
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.


Please try:

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

SELECT TOP 1 id FROM  parent
order by id asc