40Alpha 40Alpha - 2 days ago 5
SQL Question

Troubles isolating target cell in recursive sql query

I have a table, let's say it looks like this:

c | p
=====
|1|3|
|2|1|
|7|5|


c
stands for
current
and
p
stands for
parent


Given a
c
value of
2
I would return its top most ancestor (which has no parent) this value is
3
. Since this is a self referencing table, I figured using CTE would be the best method however I am very new to using it. Nevertheless, I gave it a shot:

WITH Tree(this, parent)
AS(
SELECT
c
,p
FROM myTable
WHERE c = '2'
UNION ALL
SELECT
M.c
,M.p
FROM myTable M
JOIN Tree T ON T.parent = M.c
)
SELECT parent from Tree


However this returns:

1


3


I only want
3
though. I have tried putting
WHERE T.parent <> M.c
but that doesn't entirely make sense. Neadless to say, I am a little confused for how to isolate the grandparent.

Answer
DECLARE @Table AS TABLE (Child INT, Parent INT)
INSERT INTO @Table VALUES (1,3),(2,1),(7,5)

;WITH cteRecursive AS (
    SELECT
       OriginalChild = Child
       ,Child
       ,Parent
       ,Level = 0
    FROM
       @Table
    WHERE
       Child = 2

    UNION ALL

    SELECT
       c.OriginalChild
       ,t.Child
       ,t.Parent
       ,Level + 1
    FROM
       cteRecursive c
       INNER JOIN @Table t
       ON c.Parent = t.Child
)


SELECT TOP 1 TopAncestor = Parent
FROM
    cteRecursive
ORDER BY
    Level DESC

Use a recursive cte to Recuse up the tree until you cannot. Keep track of the Level of recursion, then take the last level of recursions parent and you have the top ancestor.

And just because I wrote it I will add in if you wanted to find the top ancestor of every child. The concept is still the same but you would need to introduce a row_number() to find the last level that was recursed.

DECLARE @Table AS TABLE (Child INT, Parent INT)
INSERT INTO @Table VALUES (1,3),(2,1),(7,5),(5,9)

;WITH cteRecursive AS (
    SELECT
       OriginalChild = Child
       ,Child
       ,Parent
       ,Level = 0
    FROM
       @Table

    UNION ALL

    SELECT
       c.OriginalChild
       ,t.Child
       ,t.Parent
       ,Level + 1
    FROM
       cteRecursive c
       INNER JOIN @Table t
       ON c.Parent = t.Child
)

, cteTopAncestorRowNum AS (
    SELECT
       *
       ,TopAncestorRowNum = ROW_NUMBER() OVER (PARTITION BY OriginalChild ORDER BY Level DESC)
    FROM
       cteRecursive
)

SELECT
    Child = OriginalChild 
    ,TopMostAncestor = Parent
FROM
    cteTopAncestorRowNum
WHERE
    TopAncestorRowNum = 1
Comments