SQL Server: How to get top most parent id from the nth level in parent-child hierarchy

In the last week one of my team member who worked on ICD10¬†project asked me one question where he wanted to find the top most icd-code id from any child level. So in today’s post we’ll learn about how to get the top most parent id or let say root id from the leaf level or from the nth level in parent-child hierarchy. Hmm sounds interesting.

SQL Server 2005/2008 comes with a major tsql enhancement which really ease the life of developer. CTE(Common Table Expression) is one of them. CTE is a temporary result set which you can reference it in SELECT, UPDATE, INSERT or DELETE statement. There are two types of CTEs in SQL Server – Recursive and non-recursive. To achieve our expected result we need to use here recursive CTE. By not going into much detail, let me give you the short description about it. A recursive CTE is one that reference itself or call itself within that CTE and continues to execute until the query return the whole result. Let’s dive into it and see it in action.

For demo purpose, i have defined the table variable here with the required data in it.


DECLARE @Tab_ICDCodes TABLE (
CodeID [int] NOT NULL,
CodeName [varchar](250) NULL,
Code [varchar](250) NULL,
ParentID [int] NULL
)

INSERT INTO @Tab_ICDCodes(CodeID, CodeName, Code, ParentID)
VALUES  (1, 'Abdominal Pain', 'AP', 0),
	(2, 'Epigastric', 'EP', 1),
	(3, 'Ruptured', '', 2),
	(4, 'Acute abdomen', '', 2),
	(5, 'Aortic aneurysm', '', 3),
	(6, 'Nail', 'N', 0),
	(7, 'Nail Pain', 'N', 6)

Now to answer the original question here is the query which return top most parent / root – code id and code name. In below query we’ll pass child id as variable and query will return its top most parentid.


DECLARE @CodeId INT = 5

;WITH cteGetRootID
As
(

	SELECT 
		CodeID, CodeName, ParentID, 1 AS CodePosition
	FROM 
		@Tab_ICDCodes WHERE CodeID = @CodeId
	UNION All
	SELECT 
		ic.CodeID, ic.CodeName, ic.ParentID, CodePosition + 1
	FROM @Tab_ICDCodes ic
	INNER JOIN cteGetRootID cte ON ic.CodeID = cte.ParentID
)
SELECT TOP 1 CodeID, CodeName FROM cteGetRootID
ORDER BY CodePosition DESC

So, that’s it guys. Hope you like this post.

Advertisements