CODEWITHSUNDEEP
sql-servert-sqlcommon-table-expressionrecursive-query
Sabyasachi Mukherjee
Sabyasachi Mukherjee

Reputation: 303

How to get the root in a hierarchy query using SQL Server from any level of Hierarchy

I would like to get the Top most Ancestor (Root) of the hierarchy from any level of data. The following is my table.

CREATE TABLE #SMGROUP (ID INT NOT NULL, GRP NVARCHAR(40), GRPCLASS INT, PARENTGRP NVARCHAR(40), PARENTGRPCLASS INT)
INSERT INTO #SMGROUP VALUES (1, 'A', 1, NULL,NULL)
INSERT INTO #SMGROUP VALUES (1, 'B', 1, NULL,NULL)
INSERT INTO #SMGROUP VALUES (1, 'C', 1, NULL,NULL)
INSERT INTO #SMGROUP VALUES (1, 'A.1', 2, 'A',1)
INSERT INTO #SMGROUP VALUES (1, 'A.2', 2, 'A',1)
INSERT INTO #SMGROUP VALUES (1, 'A.3', 2, 'A',1)
INSERT INTO #SMGROUP VALUES (1, 'B.1', 2, 'B',1)
INSERT INTO #SMGROUP VALUES (1, 'B.2', 2, 'B',1)
INSERT INTO #SMGROUP VALUES (1, 'A.3.3', 3, 'A.3',2)
INSERT INTO #SMGROUP VALUES (1, 'A.3.3.3', 4, 'A.3.3',3)
INSERT INTO #SMGROUP VALUES (1, 'A.3.3.3.1', 5, 'A.3.3.3',4)
INSERT INTO #SMGROUP VALUES (1, 'B.1.2', 3, 'B.1',2)
INSERT INTO #SMGROUP VALUES (1, 'B.2.1', 3, 'B.2', 2)
SELECT * FROM #SMGROUP

I Would like to have the value of - 'A' if I provide 'A.1' as input, also the return value would be 'A' if I provide 'A.3.3' as input. Also the return would be 'A' if the parameter is 'A.3.3.3.1'

I have written some thing like this, but I am not sure how to continue after this.

;WITH items AS (
    SELECT G.GRP ,CAST('' AS NVARCHAR(30)) AS ParentGroup,
     0 AS Level    
    FROM #SMGROUP G 
    WHERE G.PARENTGRP  IS NULL  

    UNION ALL

    SELECT G.GRP, CAST(G.PARENTGRP AS NVARCHAR(30)) AS ParentGroup
    , Level + 1    
    FROM #SMGROUP G 
    INNER JOIN items itms ON itms.GRP = G.PARENTGRP 
)
SELECT * FROM items

Upvotes: 0

Views: 787

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

You are on the right direction, you just need one last push.

Instead of using a "standard" recursive cte that traverse from root to leaf nodes, you "reverse" the process and traverse from the input node back to the root. Then it's simply a top 1 with level desc in the order by clause:

DECLARE  @GRP NVARCHAR(40) = 'A.3.3.3.1';

WITH items AS (
    SELECT  G.GRP,
            ISNULL(G.PARENTGRP, '')  AS ParentGroup,
            0 AS Level    
    FROM #SMGROUP G 
    WHERE G.GRP = @GRP

    UNION ALL

    SELECT  G.GRP, 
            G.PARENTGRP, 
            Level + 1    
    FROM #SMGROUP G 
    INNER JOIN items itms 
        ON itms.ParentGroup = G.GRP
)

SELECT TOP 1 Grp
FROM items
ORDER BY Level DESC

Upvotes: 1

Related Questions