Common Table Expression For Sorting Hierarchical Records By Depth
- Written by Boris Drajer
- Published in Databases
I tried to find this on the net, and was unable to find a satisfying and simple solution. How do you retrieve hierarchical records (say, from a self-referencing table) sorted by their depth in the hierarchy? Microsoft SQL Server 2005/2008 has a new SQL construct, Here’s one example: a Category table with an ID (primary key) and ParentID (pointing to the hierarchical parent).
WITH CategoryCTE(ID, ParentID, Depth) AS ( SELECT ID, ParentID, 0 FROM Category WHERE ParentID IS NULL – root records UNION ALL SELECT cRecursive.ID, cRecursive.ParentID, cCte.Depth+1 FROM Category AS cRecursive JOIN CategoryCTE AS cCte ON cRecursive.ParentID = cCte.ID ) SELECT * FROM CategoryCTE ORDER BY Depth