title: SQL recursive query
body: <p>I have a Table Category,</p>
  
  <p>1) Id<br/>
  2) CategoryName<br/>
  3) CategoryMaster<br/></p>
  
  <p>with data as:<br/><br/>
  1 Computers 0<br/>
  2 Software 1<br/>
  3 Multimedia 1<br/>
  4 Animation 3<br/>
  5 Health         0<br/>
  6 Healthsub  5<br/></p>
  
  <p>and i have created recursive query as:</p>
  
  <pre><code> ;WITH CategoryTree AS
   (
    SELECT *, CAST(NULL AS VARCHAR(50)) AS ParentName, 0 AS Generation    
    FROM dbo.Category    
    WHERE CategoryName = 'Computers'
  
    UNION ALL        
  
    SELECT Cat.*,CategoryTree.CategoryName AS ParentName, Generation + 1    
    FROM dbo.Category AS Cat  INNER JOIN 
    CategoryTree ON Cat.CategoryMaster = CategoryTree.Id
   )
  
   SELECT * FROM CategoryTree
  </code></pre>
  
  <p>I get the results for parent category to bottom, like i get all sub categories for computer</p>
  
  <p>but i want the results from bottom-up like from Animation to Computers, please can some one suggest me right direction.</p>
  
  <p>Thank you in advance :)</p>