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

  • 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...? – ypercubeᵀᴹ Apr 11 '14 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 '14 at 16:43
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

  • 2
    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). – ypercubeᵀᴹ Apr 11 '14 at 18:23
  • 1
    Thanx, it works like a charm... I was missing the re use of the original row Id – PAP Apr 14 '14 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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