title: SQL Server return records where the most recent matches the criteria
body: <p>This is a question using SQL Server. Is there a more elegant way of doing this?</p>
  
  <p>Considering a table <code>mytable</code> (I'm using Unix timestamps, I've converted these to readable dates for ease of reading)</p>
  
  <pre><code>ID  Foreign_ID  Date
  -------------------------
  1   1           01-Jul-15
  2   2           01-Sep-16
  3   3           05-Aug-16
  4   2           01-Sep-15
  </code></pre>
  
  <p>I would like to extract the <code>Foreign_ID</code> where the most recent record's (highest ID) date is in a range, which is this example is the 1st January 2016 to 31st December 2016. The following works if substituting the dates for timestamps:</p>
  
  <pre><code>select distinct 
      Foreign_ID 
  from 
      mytable l1 
  where 
      (select top 1 Date 
       from mytable l2 
       where l2.Foreign_ID = l1.Foreign_ID 
       order by ID desc) &gt;= **1 Jan 2016**  
      and 
      (select top 1 Date 
       from mytable l2 
       where l2.Foreign_ID = l1.Foreign_ID 
       order by ID desc) &lt;= **31 Dec 2016**
  </code></pre>
  
  <p>That should only return <code>Foreign_ID = 3</code>. A simpler query would also return Foreign_ID 2 which would be wrong, as it has a more recent record dated out of the above ranges</p>
  
  <p>This will all form part of a bigger query</p>