title: Find multiple top level parents in CTE
body: <p>I'm writing a pretty standard CTE to recursively look up a tree and find the top parent. The top parent is determined if <code>OrganizationID_Parent</code> is null. I pass in a managerID and I want to know the toppest level organizations of the organizations they manage.</p>
  
  <p>Here's my CTE</p>
  
  <pre><code>;WITH OrgCTE AS
  (
      SELECT o.OrganizationID_Parent , o.OrganizationID, 1 AS Lvl FROM Organization o
      WHERE OrganizationID IN (SELECT OrganizationID FROM OrganizationManager WHERE AdjusterID_Mgr = @ManagerID)
  
      UNION ALL
  
      SELECT o.OrganizationID_Parent, cte.OrganizationID, Lvl + 1 AS Lvl 
      FROM dbo.Organization o
      INNER JOIN OrgCTE cte ON o.OrganizationID = cte.OrganizationID_Parent
  
  )
  
  SELECT * FROM OrgCTE
  </code></pre>
  
  <p>The issue I am having is that my results are strange. The tree is like this:</p>
  
  <pre><code>OrganizationID_Parent    OrganizationID
           NULL                  1
           NULL                  2
            2                   137
           137                  140
  </code></pre>
  
  <p>My subselect returns 1 and 140, and the whole query returns parents mismatched and incorrect data. I need the query to return 1 and 2 since they are the top parents.</p>
  
  <p>Here are the results I'm getting:</p>
  
  <pre><code>OrganizationID_Parent    OrganizationID
           NULL                  1
           137                  140
            2                   140
           NULL                 140
  </code></pre>
  
  <p>Here are the results I want:</p>
  
  <pre><code>OrganizationID_Parent    OrganizationID
           NULL                  1
           137                  140
            2                   137
           NULL                  2
  </code></pre>