Using a recursive CTE to read the root parent id of a hierarchical table

Note that this post could be considered the second part in my previously incredibly short series of 1 posts: Good Uses for SQL Server 2005 Common Table Expressions - previous entries being:

• Part 1 - Determining the top and bottom n pieces of information in a set
• Part 0 - Introduction

--

Ok, so the title of the post is a bit of a mouthful - sorry about that. I was asked this as a question today, and as it had been a little while since I had looked at CTEs I thought it would be a good exercise to sit down and work it out.

Consider the following table, called Amoeba. (Amoebas are useful in this context because they only have one parent, as far as I am aware)

The amoebas with id 1 and 6 are “ultimate ancestors”, as they don’t have a parent. What we need to do is for any given an AmoebaId, find out the AmoebaId of its ultimate ancestor.

Doing this with a recursive CTE is actually pretty straight forward:

 1
 2DECLARE @CurrentAmoebaId int, @UltimateAncestorId int
 3SET @CurrentAmoebaId = 5
 4;WITH ParentAmoebas (AmoebaId, ParentAmoebaId)
 5AS
 6(
 7    SELECT a.AmoebaId, a.ParentAmoebaId
 8    FROM Amoebas a
 9    WHERE p.AmoebaId = @CurrentAmoebaId
10    UNION ALL
11    -- Perform the recursive join
12    SELECT a.AmoebaId, a.ParentAmoebaId
13    FROM Amoebas a
14        INNER JOIN ParentAmoebas pa ON pa.ParentAmoebaId = a.AmoebaId
15)
16-- Grab the AmoebaId of the ultimate ancestor
17-- this will be the only entry with a null parent id
18SELECT @UltimateAncestorId = AmoebaId
19FROM ParentAmoebas
20WHERE ParentAmoebaId IS NULL
21SELECT @UltimateAncestorId

The inline comments should explain things in enough detail, but essentially the CTE will keep recursing up the page hierarchy, setting the @UltimateAmoebaId variable, until the amoeba without a amoeba id is found. At this point @UltimateAmoebaId will be set to the correct ultimate ancestor id.