Breaking News: Grepper is joining You.com. Read the official announcement!

Get Root parent of child in Hierarchical table

Abolfazl Hosseinzadeh answered on November 29, 2022 Popularity 1/10 Helpfulness 3/10

answer Get Root parent of child in Hierarchical table

Get Root parent of child in Hierarchical table

Comment

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

Popularity 1/10 Helpfulness 3/10 Language whatever

Source: stackoverflow.com

Tags: get hierarchical parent root whatever

Link to this answer
Share Copy Link