Get (Find) Top (Root) Level Parent using CTE with recursion in SQL Server

Hi nauna,

To find the Top Parent you have to use CTE with recursion.

Refer below query.

SQL

DECLARE @Test AS TABLE (category_id INT,parent_category_id INT,catname VARCHAR(20))
INSERT INTO @Test VALUES(1,0,'Auto')
INSERT INTO @Test VALUES(2,1,'Accessory')
INSERT INTO @Test VALUES(3,1,'Electronic')
INSERT INTO @Test VALUES(4,2,'Wheel')
INSERT INTO @Test VALUES(5,2,'Steering')


DECLARE @CatId INT 
SET @CatId = 3
 
;WITH cteGetRootID
As
( 
    SELECT category_id,parent_category_id,catname, 1 AS Position
    FROM @Test WHERE category_id = @CatId
    UNION All
    SELECT ic.category_id,ic.parent_category_id,ic.catname,Position + 1
    FROM @Test ic
    INNER JOIN cteGetRootID cte ON ic.category_id = cte.parent_category_id
)
SELECT TOP 1 category_id,parent_category_id,catname FROM cteGetRootID
ORDER BY Position DESC

Output

category_id    parent_category_id    catname

        1                         0                    Auto