ARNDG2 ARNDG2 - 3 days ago 5
SQL Question

T-SQL 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) 

Performance test : MAX(...) Vs. MAX(Case...)

with t(n) as (select 0 union all select n+1 from t where n<9)
select      getdate() - RAND(cast(NEWID() as varbinary))*365*10  as dt 
into        #t 
from        t t0,t t1,t t2,t t3,t t4,t t5,t t6,t t7

select max(dt) from #t

X 10

Total execution time 103977 miliseconds

select max(case when dt <= '2016-01-01 00:00:00' then dt end) from #t

X 10

Total execution time 118738 miliseconds


10 X 100M rows:                    14.761 sec
Average difference per 100M rows:   1.476 sec
Average difference per 1M rows:     0.015 sec
Comments