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.