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?

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)