title: CTE Recursion to get tree hierarchy
body: <p>I need to get an ordered hierarchy of a tree, in a specific way. The table in question looks a bit like this (all ID fields are uniqueidentifiers, I've simplified the data for sake of example):</p>
  
  <pre>EstimateItemID    EstimateID    ParentEstimateItemID     ItemType
  --------------    ----------    --------------------     --------
         1              A                NULL              product
         2              A                  1               product
         3              A                  2               service
         4              A                NULL              product
         5              A                  4               product
         6              A                  5               service
         7              A                  1               service
         8              A                  4               product</pre>
  
  <p>Graphical view of the tree structure (* denotes 'service'):</p>
  
  <pre>
             A
         ___/ \___
        /         \
      1            4
     / \          / \
    2   7*       5   8
   /            /
  3*           6*
  </pre>
  
  <p>Using this query, I can get the hierarchy (just pretend 'A' is a uniqueidentifier, I know it isn't in real life):</p>
  
  <pre><code>DECLARE @EstimateID uniqueidentifier
  SELECT @EstimateID = 'A'
  
  ;WITH temp as(
      SELECT * FROM EstimateItem
      WHERE EstimateID = @EstimateID
  
      UNION ALL
  
      SELECT ei.* FROM EstimateItem ei
      INNER JOIN temp x ON ei.ParentEstimateItemID = x.EstimateItemID
  )
  
  SELECT * FROM temp
  </code></pre>
  
  <p>This gives me the children of EstimateID 'A', but in the order that it appears in the table. ie:</p>
  
  <pre>EstimateItemID
  --------------
        1
        2
        3
        4
        5
        6
        7
        8</pre>
  
  <p>Unfortunately, what I need is an ordered hierarchy with a result set that follows the following constraints:</p>
  
  <pre>
  1. each branch must be grouped
  2. records with ItemType 'product' and parent are the top node 
  3. records with ItemType 'product' and non-NULL parent grouped after top node 
  4. records with ItemType 'service' are bottom node of a branch
  </pre>
  
  <p>So, the order that I need the results, in this example, is:</p>
  
  <pre>EstimateItemID
  --------------
        1
        2
        3
        7
        4
        5
        8
        6
  </pre>
  
  <p>What do I need to add to my query to accomplish this?</p>