title: SQL: Inner Join return one row based on criteria
body: <p>This is probably simple, but i'm looking for the raw SQL to perform an <code>INNER JOIN</code> but only return one of the matches on the second table based on criteria.</p>
  
  <p>Given two tables:    </p>
  
  <pre><code>**TableOne**   
   ID  Name  
   1   abc  
   2   def
  
  **TableTwo**  
   ID    Date     
   1     12/1/2014
   1     12/2/2014
   2     12/3/2014
   2     12/4/2014
   2     12/5/2014
  </code></pre>
  
  <p>I want to join but only return the latest date from the second table:</p>
  
  <pre><code>Expected Result:
  1   abc   12/2/2014
  2   def   12/5/2014
  </code></pre>
  
  <p>I can easily accomplish this in LINQ like so:</p>
  
  <pre><code>TableOne.Select(x=&gt; new { x.ID, x.Name, Date = x.TableTwo.Max(y=&gt;y.Date) });
  </code></pre>
  
  <p>So in other words, what does the above LINQ statement translate into in raw SQL?</p>