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 ?