title: Recursive Child/Parent queries in T/SQL
body: <p>I am using T/SQL in Microsoft SQL Server 2008</p>
  
  <p>I have a table</p>
  
  <pre><code>CREATE TABLE [TestTable](
  [CHILD] [int] NOT NULL,
  [PARENT] [int] NOT NULL
  ) ON [PRIMARY]
  
  GO
  </code></pre>
  
  <p>These are some values which define a parent child hierarchial relationship</p>
  
  <pre><code>CHILD PARENT
  1       2
  2       0
  3       1
  4       2
  5       0
  </code></pre>
  
  <p>Visually, this table looks like this</p>
  
  <pre><code>0
     2
        1
           3
        4
     5
  </code></pre>
  
  <p>I would ideally like the values to be shown as follows (where the right hand column indicates the generation)</p>
  
  <pre><code>CHILD    GENERATION
   0          0
   2          1
   1          2
   3          3
   4          2
   5          1
  </code></pre>
  
  <p>My T/SQL code looks like this</p>
  
  <pre><code>with n(CHILD, PARENT, GENERATION) as (
  select CHILD, PARENT,1 as GENERATION from TestTable
  where PARENT=0 
  union all
  select nplus1.CHILD, nplus1.PARENT, GENERATION+1 from TestTable as nplus1, n
  where nplus1.PARENT=n.CHILD 
  )
  select CHILD,GENERATION from n
  </code></pre>
  
  <p>However it doesn't work!</p>
  
  <p>It returns</p>
  
  <pre><code>CHILD   GENERATION
  2        1
  5        1
  1        2
  4        2
  3        3
  </code></pre>
  
  <p>It has the right generation, but the wrong sort order!
  Does anyone have any ideas how to solve this?</p>
  
  <p>Thank you!</p>