danywarner danywarner - 6 months ago 43
SQL Question

Find the highest grandparent on self-referencing table SQL Server

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.

Answer

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)