Menu

Common Table Expression For Sorting Hierarchical Records By Depth

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

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

Na vrh

Joomla! Debug Console

Sesija

Informacije profila

Iskorišćenost memorije

Upiti baze podataka