3

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
Fantaftw
  • 95
  • 1
  • 6
  • The lowest common ancestor to what? Please provide an example of your requirements and of your data set: do you have two fields, one that is an id and one that is the parent id of the record? – Vincenzo Maggio Jun 20 '12 at 12:57
  • did some edit, hope its somewhat easier to understand now – Fantaftw Jun 20 '12 at 13:03
  • Is Microsoft SQL? If yes I have a link to doc showing the answer, but I don't wanna post it if I'm not sure about the SQL Server you're using! :) – Vincenzo Maggio Jun 20 '12 at 13:06
  • woops yea, its Microsoft SQL. I would be very grateful if you could provide the link – Fantaftw Jun 20 '12 at 13:07
  • Well, I'm not posting it as an answer because it needs to be worked out but this is the tech link: http://technet.microsoft.com/en-us/library/bb677173.aspx This describes the main hierarchyid API and how to find LCA with a DLL in C#. I'm trying to find something ready and simple, if I can I'll post an answer, so stay tuned! :D – Vincenzo Maggio Jun 20 '12 at 13:10
  • And this SO post http://stackoverflow.com/questions/3119860/how-do-you-get-all-ancestors-of-a-node-using-sql-server-2008-hierarchyid has an answer with a query to get all the ancestors of a record: I think it could be figured out how to cross result for two records and get common ancestors... – Vincenzo Maggio Jun 20 '12 at 13:13

1 Answers1

1

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.

Tegiri Nenashi
  • 3,066
  • 2
  • 19
  • 20