title: SQL Server hierarchy, selecting parent nodes for distinct IDs
body: <p>Stuck on this hierarchy problem, so here goes.
  I have the following hierarchy table, which has been truncated.</p>
  
  <p><a href="https://i.stack.imgur.com/wEj9M.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wEj9M.png" alt="HierarchyIDs"></a></p>
  
  <p>The hierarchy has been joined to another table on the 'codes' column, with the following sample result. Let's call this table1.</p>
  
  <p><a href="https://i.stack.imgur.com/ThDX7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ThDX7.png" alt="table with hierarcjy"></a></p>
  
  <p>For each distinct ID in table1, I want to utilize the hierarchy to find a parent, if it exists.
  For example, under the codes column, 18 is the parent of 19.
  I would like to fold or group the 19 with 18 and eliminate that row.
  The desired result is something like this.</p>
  
  <p><a href="https://i.stack.imgur.com/O3JDi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/O3JDi.png" alt="desired result"></a></p>
  
  <p>This does sort of what I want, using the GetAncestor method:</p>
  
  <pre><code>SELECT A.OrgNode, A.Codes, A.ID, B.OrgNode, B.Codes, B.ID FROM table1 A INNER JOIN table1 B ON A.OrgNode.GetAncestor(1) = B.OrgNode WHERE A.ID = B.ID
  </code></pre>