title: SQL Server query. JOIN by latest date
body: <p>I have 3 tables: </p>
  
  <pre><code>UnitInfo(UnitID, ...), 
  UnitList(UnitID, ...) 
  UnitMonitoring(RecordID,UnitID, EventDate, ...)
  </code></pre>
  
  <p><code>UnitList</code> is a subset of <code>UnitInfo</code> (in terms of data and in terms of columns).  <code>UnitMonitoring</code> receives records time to time pertaining to <code>UnitList</code> (for every <code>UnitID</code> in <code>UnitMonitoring</code> we will have many records) filling <code>EventDate</code>.  (<code>UnitInfo</code> has extended info).</p>
  
  <p>I can't figure how to build a query so that for every <code>UnitID</code> in <code>UnitList</code> I get <code>UnitMonitoring</code> record such that <code>EventDate</code> is the latest one.</p>
  
  <p>So far I have </p>
  
  <pre><code>SELECT a.UnitID, a.Name, b.EventDate 
  FROM UnitInfo a INNER JOIN UnitMonitoring b 
  WHERE a.UnitID IN (SELECT UnitID FROM UnitList) 
  </code></pre>
  
  <p>which yields all records from <code>UnitMonitoring</code> </p>