title: Order of Recursion (SQL Server CTE)
body: <p>I can achieve recursion by using SQL Server's With command (CTE). </p>
  
  <pre><code>WITH MyCTE(ParentID,ID,Name,Level)
  AS
  (
  SELECT ManagerID AS ParentID, UserID AS ID, UserName AS Name, 0 AS Level 
  FROM USERS U       
  WHERE U.ManagerID IS NULL
  
  UNION ALL
  
  SELECT U.ManagerID AS ParentID, U.UserID AS ID, U.UserName AS Name, H.Level+1 AS Level
  FROM USERS U
  INNER JOIN MyCTE H ON H.ID = U.ManagerID
  )
  
  SELECT ParentID,ID FROM MyCTE
  </code></pre>
  
  <p>returns</p>
  
  <pre><code>ParentID    ID
  NULL        1
  1           2
  1           3
  2           4
  </code></pre>
  
  <p>What I want to achieve is to reverse this result set. Namely,reversing the root node and the deepest child node as,</p>
  
  <pre><code>ParentID    ID
  NULL        4
  4           2
  2           1
  3           1
  </code></pre>
  
  <p>Couldn't figure out how to programmatically implement this (preferably by using CTE), like by using a parameter to determine the recursion order etc. Any help is greatly appreciated, thanks.</p>
  
  <p>Edit :</p>
  
  <p>Modified this a bit inserting my first CTE's results into a temp table, then using another recursion I reverse the order as (I know "WHERE T.ID = (SELECT MAX(ID) FROM @tmp)" wont work in a real situation, I also gotta determine the deepest node with the "Level" column, just tried to simplify this for this example),</p>
  
  <pre><code> INSERT INTO @tmp
   SELECT ParentID,ID,Level FROM MyCTE
   WITH MyCTE2(ParentID,ID,Level)
   AS
   (
   SELECT NULL AS ParentID, ID AS ID, 0 AS Level FROM @tmp T 
   WHERE T.ID = (SELECT MAX(ID) FROM @tmp)
  
   UNION ALL
  
   SELECT R2.ID AS ParentID, T.ParentID AS ID, R2.Level+1 FROM @tmp T
   INNER JOIN MyCTE2 R2 ON R2.ID = T.ID
   WHERE T.ParentID IS NOT NULL
   )
  </code></pre>
  
  <p>Original Results (removed the 1,3 pair)</p>
  
  <pre><code>ParentID   ID   Level
    NULL      1     0
      1       2     1
      2       4     2
  </code></pre>
  
  <p>Reversed results,</p>
  
  <pre><code>ParentID   ID   Level
    NULL      4     0
      4       2     1
      2       1     2
  </code></pre>
  
  <p>Edit 2:</p>
  
  <p>I did something like this,</p>
  
  <pre><code>SELECT TTT.ParentID,TTT.ID,TTT.Level FROM
  (
  SELECT ParentID,ID,Level FROM MyCTE2
  UNION ALL
  SELECT TT.ID AS ParentID,TT.ParentID AS ID,(SELECT Level+1 FROM @tmp WHERE ID=TT.ID) 
  AS  Level  FROM
  (
  SELECT ID FROM @tmp
  EXCEPT
  SELECT ID FROM MyCTE2
  )T INNER JOIN @tmp TT ON TT.ID = T.ID
  )TTT
  ORDER BY TTT.Level
  </code></pre>
  
  <p>gives,</p>
  
  <pre><code>ParentID    ID  Level
  NULL        4   0
  4           2   1
  2           1   2
  3           1   2
  </code></pre>
  
  <p>This may contain errors, im not sure yet, just wanted to show to make sure that pair (3,1) is whther correct with level 2 ? Been thinking on this for quite a while now, I might make some silly mistakes.</p>