title: T-SQL MAX function alternatives
body: <p>I currently have this query that returns the most recent IDs in a table:</p>
  
  <pre><code>SELECT StatusUpdates.ForeignId,
  StatusUpdates.DateUpdated AS MostRecentChange
  FROM hjStatusUpdates StatusUpdates
  INNER JOIN (
      SELECT ForeignId,
      MAX(DateUpdated) AS MostRecentChange
      FROM hjStatusUpdates
      GROUP BY ForeignId) DerivedTable
      ON StatusUpdates.ForeignId = DerivedTable.ForeignId
      AND StatusUpdates.DateUpdated = DerivedTable.MostRecentChange
  WHERE StatusUpdates.ForeignTable = 'hjClientAccounts';
  </code></pre>
  
  <p>My question is, is it possible to set an upper limit on the <code>MAX</code> function, or are there better functions to use, so that I can add a "less than (date)" clause to this function?</p>
  
  <p>I do not want to add something like <code>AND DerivedTable.MostRecentChange &lt;= '2016-12-30 23:59:59'</code> to the <code>INNER JOIN</code> as this is not the solution I'm looking for.</p>