Excellent article Jeff! You just saved me quite a bit of sanity

I was needing to sort by an alphanumeric name field and finally found a suitable way that doesn't seem to break.

Here it is if anyone else has need of it.

The main difference is the HierarchicalPath.

You could essentially put ANY column in the OVER(ORDER BY) function and get a perfect sort.

Mine assumes I'll never have more than 99999 items.

WITH DirectPrograms (ProgramParent, ProgramID, ProgramDesc, ProgramInactive, ProgramAdmin, [Level], HierarchicalPath)

AS

(

SELECT p.ProgramParent, p.ProgramID, p.ProgramDesc, p.ProgramInactive, p.ProgramAdmin, [Level] = 0,

HierarchicalPath = CAST('\'+RIGHT('00000' + CAST((ROW_NUMBER() OVER (ORDER BY p.ProgramDesc)) AS VARCHAR(5)), 5) AS VARCHAR(100))

FROM ys2.PROGRAM AS p

WHERE ProgramParent IS NULL

UNION ALL

SELECT p.ProgramParent, p.ProgramID, p.ProgramDesc, p.ProgramInactive, p.ProgramAdmin, [Level] = [Level] + 1,

HierarchicalPath = CAST(d.HierarchicalPath + '\'+RIGHT('00000' + CAST((ROW_NUMBER() OVER (ORDER BY p.ProgramDesc)) AS VARCHAR(5)), 5) AS VARCHAR(100))

FROM ys2.PROGRAM AS p

INNER JOIN DirectPrograms AS d

ON p.ProgramParent = d.ProgramID

)

SELECT ProgramID, ProgramDesc, ProgramInactive, ProgramAdmin, [Level], HierarchicalPath

FROM DirectPrograms

ORDER BY HierarchicalPath