ARNDG2 ARNDG2 - 6 days ago 4
SQL Question

TSQL MAX() function alternatives

I currently have this query that returns the most recent IDs in a table:

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';


My question is, is it possible to set an upper limit on the MAX() function (or are there better functions to use) so that I can add a "less than (date)" clause to this function?

I do not want to add something like
AND DerivedTable.MostRecentChange <= '2016-12-30 23:59:59'
to the
INNER JOIN
as this is not the solution I'm looking for.

Answer

MAX(case when DateUpdated <= '2016-12-30 23:59:59' then DateUpdated end)