Sql hierarchyID - finding the lowest child (last node) value

Question

Lets say i have this data in my db

/

/1/

/1/1/

/1/2/

/1/2/1/

/2/1/

/2/1/1/

/2/2/1/

I want to get for each row the last child within the hirarchyId

I have tried to use the getdecendent and getancestor but it wont gives me what i need

I have tried getAncestor with negative number thinking maybe it will go from the end but no luck

Is there a built in way to get the value of a specific level from hierarchyID

Answer 1

…

select *, h.GetReparentedValue(isnull(h.GetAncestor(1),h), hierarchyid::GetRoot()/*..or '/' */) as lastnode
from
(
values (cast('/' as hierarchyid)),('/1/'),('/1/2/'),    ('/1/2/3/4/5/')
) as v(h);