Recursive CTE retain First Child

[markofsoton]

Hello i am trying to traverse a link table called..groupsg roups

what i need is get the the first found parent id for each subsequent recursion.

effectively the groupid from
the first query listed in the subseqent records

so from

Groupid, parentGroupID
2 1
3 2
4 2
5 4


so input = groupid 1

Groupid, parentGroupID, firstParentID
2 1 2
3 1 3
4 2 2
5 4 2

the intention is to create a result set i can count the number of recursive groups
under each of the immediate child results.

can it be done...

With GroupChildren (GroupID, ParentGroupId, [Level])
AS
(
Select GroupID, ParentGroupId, 0 as [Level] from GroupsGroups AS e where ParentGroupID = @groupid
UNION ALL
SELECT e.GroupID, e.ParentGroupId , [Level] + 1 from GroupsGroups as e inner join groupchildren as d on e.ParentGroupID = d.GroupID
)

select GroupID, ParentGroupID from GroupChildren

[ck9663]

You lost me somewhere.

Could you give a little mode detail explanation on your sample data and your desired output? I did not see any GroupID = 1 so I'm kind'a lost.

-- CK