Recursive sql - find the grandgrandparent of a child

Question

I have a table like the below:

Id ParentID 1 99 2 9 3 1 4 2 5 4 6 3

and I would like to have a query that gives me for each child the last ancestor.

I mean that the desired result is

id Lastancestor 1 99 2 9 3 99 4 9 5 9 6 99

I have a lot of data so I need something quick.

Thanks.

What you need is a [Recursive Common Table Expression](https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx) — Magnus, Sep 09 '15 at 20:19
and an example from the excellent help at SO: http://stackoverflow.com/questions/22909692/sql-server-cte-parent-child-recursive — xQbert, Sep 09 '15 at 20:31
`99`? Do you have a plan for handling deities, or just use a magic number for their (single) parent? How about NULL? Or zero? Or, just to be difficult, their own `Id`? Have you done any research or made any attempts at solving your problem? The idea here is that people help you solve problems, but SO is not a code writing service. — HABO, Sep 09 '15 at 23:48
@Xristos Epitropakis You would need to change your data (and i understand you probably cant do this) but the HIERARCHYID data type in T-SQL is designed for this sort of thing. Have a read of this if your interested https://msdn.microsoft.com/en-us/library/bb677213.aspx — Paul Spain, Sep 10 '15 at 00:06

Giorgos Betsos · Accepted Answer · 2015-09-09T21:14:06.007

2

You can use a Recursive CTE to accomplish this:

;WITH CTE AS (
  SELECT Id AS origId, ParentID, 0 AS lvl
  FROM mytable

  UNION ALL

  SELECT c.origId AS origId, 
         m.ParentID, lvl = lvl + 1
  FROM CTE AS c
  INNER JOIN mytable AS m ON c.ParentID = m.Id
)
SELECT origId AS id, ParentID AS Lastancestor
FROM (
  SELECT origId, ParentID,
         ROW_NUMBER() OVER (PARTITION BY origId 
                            ORDER BY lvl DESC) AS rn
  FROM CTE) AS t
WHERE t.rn = 1

Here, the anchor member of the CTE is just the whole table. Recursion goes up the tree hierarchy while propagating the original Id (as origId) down the recursion chain. The recursion terminates as soon as an empty set is returned, i.e. as soon as no more c.ParentID = m.Id matches are found.

To get the required result, i.e. the Lastancestor per id, all we need to do is fetch the record having the greatest lvl (i.e. depth) per id. This is achieved using ROW_NUMBER window function.

Demo here

edited Sep 09 '15 at 21:14

answered Sep 09 '15 at 21:08

Giorgos Betsos

71,379
9
63
98

Very nice solution! +1 from my side – Shnugo Sep 09 '15 at 21:23
One question: Did you use this approach in **real life**? I really appreciate it, but - as the OP stated - there's "lot of data" and it must be "quick". If the CTE is filled with a large table, this recursion will lead to many, many full table scans. I doubt, that there is usage of an index... What is your experience with this? Anyway, I love it and have to do some tests... – Shnugo Sep 09 '15 at 21:29
@Shnugo This is the preferred approach to query hierarchical data structures in SQL Server. The only other alternative AFAIK is the use of a cursor which is less efficient. I have never used recursive CTEs in real life though. The OP can inform us about how it performs with his actual data. – Giorgos Betsos Sep 10 '15 at 07:00

score 0 · Answer 2 · answered Sep 09 '15 at 21:05

If there is a maximum depth you could use this approach. You can add further depth levels with simple copy and past and adapt. I added one data element "19,6" to generate one with three ancestors and one with four.

Just paste this into an empty query window and execute. Adapt to your needs...

declare @Test table (Id int, ParentID int)

insert into @Test values
(1,99)
,(2,9)
,(3,1)
,(4,2)
,(5,4)
,(6,3)
,(19,6);


WITH Ancestors1 AS
(
    SELECT Test.*
          ,Ancestor.ParentID AS Anc1ID 
    FROM @Test AS Test
    LEFT JOIN @Test AS Ancestor ON Test.ParentID=Ancestor.Id
)
,Ancestors2 AS
(
    SELECT Ancestors1.*
          , Ancestor.ParentID AS Anc2ID 
    FROM Ancestors1
    LEFT JOIN @Test AS Ancestor ON Ancestors1.Anc1ID=Ancestor.Id
)
,Ancestors3 AS
(
    SELECT Ancestors2.*
          , Ancestor.ParentID AS Anc3ID 
    FROM Ancestors2
    LEFT JOIN @Test AS Ancestor ON Ancestors2.Anc2ID=Ancestor.Id
)
SELECT Id,*
      ,COALESCE(Anc3ID,Anc2ID,Anc1ID,ParentID)  AS LastAncId
FROM Ancestors3

Recursive sql - find the grandgrandparent of a child

2 Answers2