CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expressionrecursive-query
Rober
Rober

Reputation: 73

SQL Find Top Parent

I have this table:

Id  ParentId    LevelName
1   0           XXXX
2   0           XXXX
4   2           XXXX
5   2           XXXX
6   2           XXXX
7   2           XXXX
8   2           XXXX
9   2           XXXX
18  2           XXXX
19  18          XXXX
20  19          XXXX

I want the top level ParentId from this like. If I will pass the Id 20 then it will return 2, which is the top level Id in the table:

 Id ParentId    LevelName
 2  0           XXXX

My attempt:

WITH cteReports (LvlID, PrntID, LevelID) AS
    (SELECT Id,
            ParentId,
            1
     FROM Levels
     WHERE Id = @ParentId
     UNION ALL
     SELECT e.Id,
            e.ParentId,
            r.LevelID + 1
     FROM Levels e
          INNER JOIN cteReports r ON e.ParentId = r.LvlID)
SELECT Id
FROM Levels
WHERE Id IN (SELECT LvlID FROM cteReports);

Upvotes: 1

Views: 108

Answers (2)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

The biggest issue was your join had the tables flipped.

DECLARE @Levels TABLE(
    ID INT,
    ParentID INT,
    LevelName VARCHAR(20)
)   
INSERT INTO @Levels (ID,ParentID,LevelName) SELECT 1   ,0           ,'XXXX'
INSERT INTO @Levels (ID,ParentID,LevelName) SELECT 2   ,0           ,'XXXX'
INSERT INTO @Levels (ID,ParentID,LevelName) SELECT 4   ,2           ,'XXXX'
INSERT INTO @Levels (ID,ParentID,LevelName) SELECT 5   ,2           ,'XXXX'
INSERT INTO @Levels (ID,ParentID,LevelName) SELECT 6   ,2           ,'XXXX'
INSERT INTO @Levels (ID,ParentID,LevelName) SELECT 7   ,2           ,'XXXX'
INSERT INTO @Levels (ID,ParentID,LevelName) SELECT 8   ,2           ,'XXXX'
INSERT INTO @Levels (ID,ParentID,LevelName) SELECT 9   ,2           ,'XXXX'
INSERT INTO @Levels (ID,ParentID,LevelName) SELECT 18  ,2           ,'XXXX'
INSERT INTO @Levels (ID,ParentID,LevelName) SELECT 19  ,18          ,'XXXX'
INSERT INTO @Levels (ID,ParentID,LevelName) SELECT 20  ,19          ,'XXXX'

DECLARE @TargetID INT=20;

WITH cteReports (Id, ParentId, LevelID) AS
(
    SELECT Id,
        ParentId,
        1
    FROM @Levels
    WHERE Id = @TargetID
    UNION ALL
    SELECT e.Id,
        e.ParentId,
        r.LevelID + 1
    FROM @Levels e
    INNER JOIN cteReports r ON r.ParentId = e.Id
)
SELECT lvl.*
FROM @Levels lvl
INNER JOIN cteReports cte ON cte.ID=lvl.ID
WHERE LevelID = (
    SELECT MAX(LevelID) AS MaxLevelID
    FROM cteReports
)

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272106

This should do it:

DECLARE @start_id INT = 20;
WITH rcte AS (
    SELECT *
    FROM t
    WHERE id = @start_id

    UNION ALL

    SELECT curr.*
    FROM t    AS curr
    JOIN rcte AS prev ON curr.id = prev.parentid
)
SELECT *
FROM rcte
WHERE parentid = 0

Upvotes: 3

Related Questions