title: SQL Server CTE -Find top parentID forEach childID?
body: <p>I have a table which contains hierarchy data - something like: </p>
  
  <pre><code>childID  |  parentID
  ____________________
    1      |     5
    5      |     9
    9      |     20
    2      |     4
    3      |     7
    7      |     8
    8      |     8
   20      |     20
    4      |     4
    8      |     8
  </code></pre>
  
  <p>desired output: </p>
  
  <p><img src="https://i.stack.imgur.com/ztnGl.jpg" alt="enter image description here"></p>
  
  <p>I've created a recursive CTE which finds me the top <code>fatherID</code>.</p>
  
  <p>Something like: </p>
  
  <pre><code>;WITH cte AS (
                   SELECT a.childID
                         ,a.parentID
                         ,1 AS lvl
                   FROM   [Agent_Agents] a
                   WHERE   a.childID = 214 //&lt;==== value to begin with !! - thats part the problem
                   UNION ALL
                   SELECT tmp.childID
                         ,tmp.parentID
                         ,cte.lvl+1
                   FROM   [Agent_Agents] tmp
                           INNER JOIN cte  ON  tmp.childID = cte.parentID
                   WHERE   cte.childID&lt;&gt;cte.parentID
               )
  SELECT *
  FROM   cte
  WHERE   lvl = (
              SELECT MAX(lvl)
              FROM   cte
          )
  </code></pre>
  
  <p><strong>The problem:</strong> </p>
  
  <p>I executed the CTE with <strong>explicit</strong> <code>childID</code> value to begin with (214) !
  So it gives me the value for 214 only.
  the CTE do the recursive part and find topParent for childID.</p>
  
  <p><strong>but</strong>
  I want <code>ForEach row in the Table</code> - to execute the CTE with the <code>childID</code> value !</p>
  
  <p>I have tried to do it with <code>CROSS APPLY</code>: </p>
  
  <p>Something like: </p>
  
  <pre><code>select * from myTable Cross Apply (
                                       ;WITH cte AS (....)
                                    )
  </code></pre>
  
  <p>but IMHO (from my testing !!)  - its impossible.</p>
  
  <p>The other idea of putting the recursive CTE in a UDF has a performance penalty (udf's problem as we know).</p>
  
  <p>How can I create this query so that it'll actually work? (  or some near solution )?</p>
  
  <p>here is what I've tried </p>
  
  <p><a href="https://data.stackexchange.com/stackoverflow/query/edit/69458" rel="nofollow noreferrer">https://data.stackexchange.com/stackoverflow/query/edit/69458</a></p>