CTE to traverse back up a hierarchy?
Ah, I figured it out:
WITH Hierarchy(TaskID, [Subject], ParentID, IsProject, HLevel)AS( SELECT TaskID, [Subject], ParentID , IsProject, 0 as HLevel FROM Task WHERE TaskID = 59 UNION ALL SELECT SubDepartment.TaskID, SubDepartment.[Subject], SubDepartment.ParentID , SubDepartment.IsProject, HLevel + 1 FROM Task SubDepartment INNER JOIN Hierarchy ParentDepartment ON SubDepartment.TaskID = ParentDepartment.ParentID )SELECT TaskID, [Subject], ParentID, IsProject, HLevelFROM HierarchyORDER BY HLevel DESC