title: MSSQL CTE tree with filter how to?
body: <p>There's a table that describes object hierarchy and their type:</p>
  
  <pre><code>DECLARE @TTable TABLE
  (
   ID int
   ,Higher int -- for some reason this is how ID_PARENT named
   ,[Type] int
  )
  </code></pre>
  
  <p>I need to select a subtree with branches only containing nodes of certain [Type]. Since I'm a beginner at SQL generally and CTE I did this by first finding all the branches that contain objects of certain type from bottom to top:</p>
  
  <pre><code>;WITH c
  AS
  (
  SELECT 
      ID
      ,Higher
      ,[Type]
  FROM
      dbo.EnObj
  WHERE Type = 40
  
  UNION ALL
  
  SELECT
      t.ID
      ,t.Higher
      ,t.Type
  FROM
      dbo.EnObj T
  INNER JOIN c ON t.ID = c.Higher
  )
  </code></pre>
  
  <p>Then chose unique records into a table variable:</p>
  
  <pre><code>INSERT @TTable
  SELECT DISTINCT
  c.ID
  ,c.Higher
  ,c.Type
  ,c.Name
  ,c.Voltage
  FROM  c
  </code></pre>
  
  <p>And then built an hierarchy by CTE with sorting from top to bottom:</p>
  
  <pre><code>;WITH CTE AS (
      SELECT
      ID
      ,Higher
      ,[Type]
  ,CAST(ID AS VARCHAR(128)) AS Sort
  FROM    
      @TTable
  WHERE
      Higher IS NULL
  UNION ALL
  SELECT
      N.ID
      ,N.Higher
      ,N.[Type]
      ,CAST(P.Sort + '/' + CAST(N.ID AS VARCHAR) AS VARCHAR(128))
  FROM    
      @TTable N
  INNER JOIN CTE AS P ON P.ID = N.Higher
  )
  SELECT 
      ID
      ,Higher
      ,[Type]
      ,Sort
  FROM CTE 
  ORDER BY Sort
  </code></pre>
  
  <p>It works, but I wonder if there's a way to optimize this query, like using one CTE instead of two or have a filter in CTE.</p>