0

I have hierarchyid in my tables like this. 

/1/ - This is Category 
/1/1/ - This is SubCategory
/1/1/1/ - This is Item
/1/1/2/ - Item
/1/1/3/ - Item
/1/2/ - SubCategory
/1/2/1/ - Item
/1/2/2/ - Item
/1/2/3 - Item

I want to get the last child regardless of Category, Subcategory or Items using SQL Query. For Eg. If its category, It will return /1/ ; If It's Subcategory It should return /1/ or /2/ (second child/last child); If it's Item it should return 3rd child/last child.

I tried this query. But, it's returning me first part only.

SELECT PricedItemHID.GetAncestor(PricedItemHID.GetLevel() -1) FROM ItemsList
Yesudass Moses
  • 1,841
  • 3
  • 27
  • 63
  • Do you really store data slash separated, like '/1/1/'? (Or is it saparate columns?) – jarlh Jun 21 '15 at 09:39
  • Its HierarchyID datatype on SQL Server 2008; It's stored as like this. – Yesudass Moses Jun 21 '15 at 09:40
  • 1
    I see. Thanks for answering! (I don't know SQL Server very well...) – jarlh Jun 21 '15 at 09:44
  • Is it you just want `/1/` (or `/2/`, or `/3/`)? Essentially the last three characters? You'd probably have to convert to char and then search/substring out. It doesn't look like there's anything to return the "id" of the level per se; the documentation claims it's only the **logical** representation, so the current value might not be what you expect. – Clockwork-Muse Jun 21 '15 at 12:06

1 Answers1

0

You can use something like this : 

Select @MaxChild = Max(OrgNode) from HumanResources.EmployeeDemo

this link can help you [http://blogs.msdn.com/b/manisblog/archive/2007/08/28/sql-server-2008-hierarchyid-part-ii.aspx][1]

Mostafa Marji
  • 245
  • 8
  • 19