title: SQL Server: select based on the latest id in a many to n:m relationship
body: <p>What i need to do is select the comment details and the last action taken on the comment; I have 3 tables:</p>
  
  <p>Comment</p>
  
  <pre><code>CommentID, commentText, userID, date_posted
  </code></pre>
  
  <p>Action</p>
  
  <pre><code>ActionID, action_taken,userID,date_actioned
  </code></pre>
  
  <p>and CommentJoinAction</p>
  
  <pre><code>id,ActionID,CommentID
  </code></pre>
  
  <p>There can be one comment, but many actions on the comment.</p>
  
  <p>my SQL is looking something like:</p>
  
  <pre><code>Select /*snip comment details and such*/
  From Comment
  Inner Join (select max(actionid) from commentjoinaction) as cja on /*blah cause you know from reading this, it won't work*/
  </code></pre>
  
  <p>So what is it that I can do so that I always pick up the latest commentAction for the comments.</p>
  
  <p>many thanks</p>