4

I have a table with a hierarchy ( with a recursive parentID column), for one record, I know how to get the last parent record, with the following code :

declare @Id integer

;WITH CTE AS
(
    SELECT  a.[Id],
            a.[ParentId]
    FROM    [Area] a WITH (NOLOCK)
    where a.[Id] = @Id 

    UNION ALL

    SELECT  a.[Id],
            a.[ParentId]
    FROM [Area] a WITH (NOLOCK)
    INNER JOIN CTE cte ON cte.[ParentId] = a.[Id]
)

SELECT top 1 a.[Id]
FROM CTE a
WHERE a.ParentId is null

It works as long as I'm working with one id, here @Id.

But I can't find a way to do the same but for a list of Ids, without using cursors, as CTE seems to be OK only when y'oure working on one record.

Could someone point me at the right direction ?

Many thanks

2
  • 1
    And what do you mean with "CTE seems to be OK ..."? What happens if you amend the query to find the roots of many IDs, using where a.[Id] IN (..list of IDs..), etc...? Apr 11, 2014 at 16:33
  • Is this a single rooted hierarchy? In other words, is there only one record with a null or empty ParentId?
    – BozoJoe
    Apr 11, 2014 at 16:43

1 Answer 1

5

Assuming the original table design is somewhat like this:

CREATE TABLE dbo.Area
(
    RowID integer PRIMARY KEY,
    GroupID  integer NOT NULL,
    ParentID integer NULL,
);

Sample data:

INSERT dbo.Area
    (RowID, GroupID, ParentID)
VALUES 
    (1, 1, NULL), -- Root
    (2, 1, 1),
    (3, 1, 2),
    (4, 2, NULL), -- Root
    (5, 2, 4),
    (6, 2, 5),
    (7, 2, 6);

The following recursive CTE query finds roots for the RowID list given in the anchor:

WITH R AS
(
    SELECT
        A.RowID,
        A.RowID AS OriginalRowID,
        A.ParentID
    FROM dbo.Area AS A
    WHERE
        A.RowID IN (3, 7)

    UNION ALL

    SELECT
        A.RowID,
        R.OriginalRowID,
        A.ParentID
    FROM dbo.Area AS A
    JOIN R
        ON R.ParentID = A.RowID
)
SELECT
    R.OriginalRowID,
    R.RowID
FROM R
WHERE
    R.ParentID IS NULL
ORDER BY
    R.OriginalRowID
OPTION (MAXRECURSION 0);

The output shows each supplied RowID and the root for that group:

Output

1
  • 3
    Yes. The query would work even if the hierarchy allowed for multiple parents of a node (it would find all root-ancestors of the selected/anchored nodes). Apr 11, 2014 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.