WITH GroupMembers (Bottom, Child, ParentGroup, Level, hierarchypath) 
AS 
( 
-- Anchor member definition 
SELECT g.child as Bottom, g.child, g.parent, 0 AS Level, convert(varchar(max), g.child + '->' + g.parent) AS hierarchypath 
FROM childrenandparents AS g 
WHERE child not in (select parent from childrenandparents) 
UNION ALL 
-- Recursive member definition 
SELECT gm.bottom, g.child, g.parent, Level + 1, hierarchypath + '->' + g.parent 
FROM childrenandparents as g 
INNER JOIN GroupMembers AS gm 
ON gm.parentgroup = g.child 
where  hierarchypath not like '%'+g.child +'->' + g.parent + '%'
) 
 
select  bottom, parentgroup, level,  hierarchypath 
from groupmembers 
where  hierarchypath like '%'+parentgroup + '->' + '%'
order by level desc 
option(maxrecursion 100);