Sql HierarchyId How do I get the last descendants?

Question

Using t-sql hierarchy Id how do I get all of the rows that have no children (that is the last decendants)?

Say my table is structured like this:

 Id, 
 Name,
 HierarchyId

And has these rows:

1, Craig, /
2, Steve, /1/
3, John, /1/1/

4, Sam, /2/
5, Matt, /2/1/
6, Chris, /2/1/1/

What query would give me John and Chris?

Alleviator

Answer 1

Perhaps there are better ways but this seams to 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

Query above will fail if node numbers is not in an unbroken sequence. Here is another version that should take care of that.

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)

Wallywalnut

Answer 2

Since you only need leafs 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 every row without a child row.

This assumes your HierarchyId is a FOREIGN KEY towards the Id, not the whole "path" as presented in your example. If it isn't, this is probably the first thing you should fix in your database model.

--- EDIT ---

OK, here is the MS SQL Server-specific query that actually 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 the IsDescendantOf considers any row a descendant of itself, so we also need the CHILD.Id <> PARENT.Id in the condition.

Krak

I'm fairly certain the OP is using the HierarchyID data type of SQL Server 2008, which explains the unusual representation (see msdn.microsoft.com/en-us/magazine/cc794278.aspx). – Cholecalciferol

@DanielPratt Ahh... I see now the question was re-tagged as [sql-server]. – Krak

Thanks for responding Branko, but in my example the Id field is an integer and the HierarchyId is a sql HierarchyId so they can't be compared. Are you saying that I need to change the key of the table to be the hierarchyId? – Alleviator

@EricNeifert I assumed you were using a "classical" design for representing the hierarchical data in relational databases, not the MS SQL Server-specific mechanism. Unfortunately, I'm not familiar enough with the MS SQL Server-specific mechanism to recommend whether you should actually make a switch, but it seems the same basic idea can be employed to MS SQL Server as well - see the Mark Bannister's answer. – Krak

Answer 3

Hi I use this one and works perfectly 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

Centro