Find the highest grandparent on self-referencing table SQL Server

Question

I have this table in SQL Server:

Parent Child 1 2 89 7 2 3 10 5 3 4

I need to build a recursive Stored Procedure that finds the maximum ascendant of any child.

For example: If I want to find the maximum ascendant of 4, it should return 1 because:

4 is the child of 3.

3 is the child of 2.

2 is the child of 1.

So I can find the ultimate parent.

Please refer recursive self sql server join..It may help you..[Recursive Self Join][1] [1]: http://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join-in-sql-server — Sagar Shirke, Aug 11 '15 at 13:59

score 6 · Accepted Answer · answered Aug 11 '15 at 14:04

A perfect job for recursive CTE:

;WITH
    cte1 AS
    (   -- Recursively build the relationship tree
        SELECT      Parent
                ,   Child
                ,   AscendentLevel = 1
        FROM        my_table
        UNION ALL
        SELECT      t.Parent
                ,   cte1.Child
                ,   AscendentLevel = cte1.AscendentLevel + 1
        FROM        cte1
        INNER JOIN  my_table    t   ON t.Child = cte1.Parent
    ),
    cte2 AS
    (   -- Now find the ultimate parent
        SELECT      Parent
                ,   Child
                ,   rn = ROW_NUMBER() OVER (PARTITION BY Child ORDER BY AscendentLevel DESC)
        FROM        cte1
    )

SELECT  *
FROM    cte2
WHERE   rn = 1
OPTION  (MAXRECURSION 0)

+1 for an excellent answer. I was working on wrapping up my answer, but I saw your response and found it better. Here is a SQL Fiddle for this if you would like to add it to your answer. http://www.sqlfiddle.com/#!3/3bf43/1/0 — FutbolFan, Aug 11 '15 at 14:09

score 0 · Answer 2 · answered Mar 08 '22 at 10:24

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 AS a INNER JOIN
                                               dbo.myTable AS 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
)
, JoinParentChild As (
    SELECT Id, ISNULL(RootParent, Id) As RootParent
    FROM CTE_myTable_RN
    WHERE RN = 1
)

SELECT  TOP(100) PERCENT JoinParentChild.Id, JoinParentChild.RootParent
FROM JoinParentChild 
ORDER BY JoinParentChild.Id

Find the highest grandparent on self-referencing table SQL Server

2 Answers2