title: Select MAX() or Select TOP 1 on Join
body: <p>I'm working with the following code to only get  one associated person per case, using the MAX Associated Type to get the top 1. </p>
  
  <p>Associated Type is not a GUID, rather looks like:</p>
  
  <p>Responsible Party, Primary Physician, etc.</p>
  
  <p>It just so happens that Responsible Party is the last alphabetical option, so it's a lucky workaround. Not every case has a responsible party, however, and if there isn't a responsible party, the next top associated person is 'good enough' and will be highlighted as a data error anyway.</p>
  
  <p>The result shows every single associated person (rather than top 1), but shows all of them as Responsible Party, which is not true. What am I doing wrong here?</p>
  
  <pre><code>FROM T_LatestIFSP Ltst
  
  LEFT OUTER JOIN ( 
      SELECT
          Clas.ClientCase_ID,
          MAX(Astp.AssociatedType) AS AssociatedType
      FROM
          T_ClientAssociatedPerson Clas
          Inner Join T_AssociatedType Astp
          ON  Clas.AssociatedType_ID = Astp.AssociatedType_ID
      GROUP BY Clas.ClientCase_ID
  ) AS Astp ON Ltst.ClientCase_ID = Astp.ClientCase_ID
  
  
  LEFT OUTER JOIN T_ClientAssociatedPerson Clas
      on Clas.ClientCase_ID = Astp.ClientCase_ID
  
  
  LEFT OUTER JOIN T_AssociatedPerson Aspr
      ON Aspr.AssociatedPerson_ID = Clas.AssociatedPerson_ID
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/XUhrG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XUhrG.png" alt="enter image description here"></a></p>