CODEWITHSUNDEEP
sqlsql-serverparenthierarchyidancestor
Fantaftw
Fantaftw

Reputation: 95

SQL: Finding the Lowest Common Ancestor of hierarchyids

I have a dataset containing (among others) the values hierarchyids and the Parent-hierarchyids. For another query I need the lowest common ancestor of the hierarchyids and since I am fairly new to sql and espacially hierarchyids.

ID          HID         ParentHID
1           0x5CB280    0x5CA0
2           0x5CABBB08  0x5CABBA
3           0x5CB270    0x5CA0

Upvotes: 3

Views: 974

Answers (1)

Tegiri Nenashi
Tegiri Nenashi

Reputation: 3086

Assuming your hierarchy is not severely balance-skewed (e.g. a list) getting ancestor chain is cheap. Next, all you have to do is take two ancestor chains, make set intersection, and then find a longest hierarchy id.

Upvotes: 1

Related Questions