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
where a.[Id] IN (..list of IDs..)
, etc...?