0

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

Y.G.J
  • 1,098
  • 5
  • 19
  • 44
  • does this help? https://stackoverflow.com/questions/8562123/sql-hierarchyid-how-do-i-get-the-last-descendants – GrahamH Aug 24 '21 at 18:14

1 Answers1

0

…

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);
lptr
  • 1
  • 2
  • 6
  • 16