danywarner - 10 months ago 83

SQL 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.

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)
```