Sql HierarchyId How to get the last descendants?

Question

Using t-sql Id hierarchy how can I get all rows that don't have children (these are the latest decands)?

Say my table is structured as follows:

  Id, Name, HierarchyId 

And has the following lines:

 1, Craig, / 2, Steve, /1/ 3, John, /1/1/ 4, Sam, /2/ 5, Matt, /2/1/ 6, Chris, /2/1/1/ 

What request will John and Chris give me?

Answer 1

There may be better ways, but these seams do the job.

 declare @T table ( ID int, Name varchar(10), HID HierarchyID ) insert into @T values (1, 'Craig', '/'), (2, 'Steve', '/1/'), (3, 'John', '/1/1/'), (4, 'Sam', '/2/'), (5, 'Matt', '/2/1/'), (6, 'Chris', '/2/1/1/') select * from @T where HID.GetDescendant(null, null) not in (select HID from @T) 

Result:

 ID Name HID ----------- ---------- --------------------- 3 John 0x5AC0 6 Chris 0x6AD6 

Update 2012-05-22

The query above will not work if the node numbers are not in a continuous sequence. Here is another version that should take care of this.

 declare @T table ( ID int, Name varchar(10), HID HierarchyID ) insert into @T values (1, 'Craig', '/'), (2, 'Steve', '/1/'), (3, 'John', '/1/1/'), (4, 'Sam', '/2/'), (5, 'Matt', '/2/1/'), (6, 'Chris', '/2/1/2/') -- HID for this row is changed compared to above query select * from @T where HID not in (select HID.GetAncestor(1) from @T where HID.GetAncestor(1) is not null) 

Answer 2

Since you only need leaves, and you don’t need to get them from a specific ancestor, a simple non-recursive query like this should do the job:

 SELECT * FROM YOUR_TABLE PARENT WHERE NOT EXISTS ( SELECT * FROM YOUR_TABLE CHILD WHERE CHILD.HierarchyId = PARENT.Id ) 

In plain English: select each row without a child row.

This assumes that your HierarchyId is a FOREIGN KEY to Id , and not the entire "path" presented in your example. If this is not the case, this is probably the first thing you should fix in your database model.

--- EDIT ---

OK, here is a specific MS SQL Server query that really works:

 SELECT * FROM YOUR_TABLE PARENT WHERE NOT EXISTS ( SELECT * FROM YOUR_TABLE CHILD WHERE CHILD.Id <> PARENT.Id AND CHILD.HierarchyId.IsDescendantOf(PARENT.HierarchyId) = 1 ) 

Note that IsDescendantOf considers any string to be a descendant of itself, so we also need CHILD.Id <> PARENT.Id in the condition.

Answer 3

Hi, I use this one and works great for me.

 CREATE TABLE [dbo].[Test]([Id] [hierarchyid] NOT NULL, [Name] [nvarchar](50) NULL) DECLARE @Parent AS HierarchyID = CAST('/2/1/' AS HierarchyID) -- Get Current Parent DECLARE @Last AS HierarchyID SELECT @Last = MAX(Id) FROM Test WHERE Id.GetAncestor(1) = @Parent -- Find Last Id for this Parent INSERT INTO Test(Id,Name) VALUES(@Parent.GetDescendant(@Last, NULL),'Sydney') -- Insert after Last Id