less3

WITH DeptHierarchy AS (
    SELECT
        ID as UnitID,
        DepartmentName as UnitName,
        ParentID as UpperUnitID,
        CAST('' AS NVARCHAR(MAX)) AS NavigationalPath,
        0 AS DepthLevel
    FROM tblDepartment
    WHERE ParentID IS NULL

    UNION ALL

    SELECT
        d.ID as UnitID,
        d.DepartmentName as UnitName,
        d.ParentID as UpperUnitID,
        CASE WHEN h.DepthLevel = 0 THEN d.DepartmentName ELSE h.NavigationalPath + '-' + d.DepartmentName END AS NavigationalPath,
        h.DepthLevel + 1
    FROM tblDepartment d
    JOIN DeptHierarchy h ON d.ParentID = h.UnitID
)
SELECT
    DepthLevel as Cnt,
    UnitName AS Department,
    NavigationalPath AS Path
FROM (
    SELECT
        TOP 1 WITH TIES
        DepthLevel,
        UnitName,
        NavigationalPath,
        UpperUnitID,
        UnitID
    FROM DeptHierarchy
    GROUP BY DepthLevel, UnitName, NavigationalPath, UpperUnitID, UnitID
    ORDER BY DepthLevel DESC
) r1
ORDER BY UpperUnitID DESC, UnitID