title: Result from CTE query not sorted by Level, why?
body: <p>Update:
  I asked the question because the result does not look like how it is executed... which is explained here:
  <a href="http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx</a></p>
  
  <p>Well I have another question...
  If I do not need the same parents and the Level, is it possible to return one parent only once using the CTE (not the select after it) or maybe some other sql?</p>
  
  <p>===========================
  Desired result is like:</p>
  
  <pre><code>......
      54          4           
      **** the above is anchor member, the numbers are correct  
      4           1          
      1           0           
      2           1           
      36          35          
      35          8           
      8           1           
      54          12          
      12          1           
      11          1           
      3           1           
  </code></pre>
  
  <p>===========================</p>
  
  <p>I am using a recursive query to find out all the parents from a Hierarchy table, for items from Items table; I thought the result should be by Level, but it is not... I know I can sort it using order by, I just think the output itself should be ordered by Level because the Recursive Memeber is run by Level, right? </p>
  
  <pre><code>            WITH Result(ItemID, ParentID, Level)
              AS
              (
                  --get the anchor member from tbItems
                  SELECT itemID, itemParentID, 0 AS Level
                  FROM   tbItems  WHERE
                      approved = 0 
                  UNION ALL
                  --recursive member from tbHierarchy
                  SELECT h.hierarchyItemID, h.parentItemID, Level + 1
                  FROM tbHierarchy AS h
                  INNER JOIN
                      Result AS r
                          ON
                          h.hierarchyItemID = r.ParentID          
  
              )
              SELECT *
              FROM Result
  </code></pre>
  
  <p>the Result is:</p>
  
  <pre><code>    ItemID      ParentID    Level
      ----------- ----------- -----------
      7           3           0
      11          2           0
      18          11          0
      19          11          0
      21          54          0
      31          2           0
      33          36          0
      34          36          0
      35          36          0
      36          36          0
      38          2           0
      39          2           0
      40          2           0
      54          4           0
      **** the above is anchor member, the numbers are correct  
      4           1           1
      1           0           2
      2           1           1
      1           0           2
      2           1           1
      1           0           2
      2           1           1
      1           0           2
      36          35          1
      35          8           2
      8           1           3
      1           0           4
      36          35          1
      35          8           2
      8           1           3
      1           0           4
      36          35          1
      35          8           2
      8           1           3
      1           0           4
      36          35          1
      35          8           2
      8           1           3
      1           0           4
      2           1           1
      1           0           2
      54          12          1
      12          1           2
      1           0           3
      11          1           1
      1           0           2
      11          1           1
      1           0           2
      2           1           1
      1           0           2
      3           1           1
      1           0           2
  </code></pre>