September 1, 2009 at 4:23 am
This seems real simple, but I will be relying on this code heavily.
I have many tables with a last_mod datetime field. When I join two tables I often want to determine the latest of the two last_mod fields (down to milliseconds or lower)
I tried datediff() but this has issues with very large differences in dates
select DATEDIFF(millisecond, '2099/12/31 11:00', '9999/12/31 11:00')
is it reliable to just compare the fields
@RetDate = case when @Date1 > @Date2 then @Date2 else @Date1
Thanks in advance
Fred
September 1, 2009 at 5:48 am
Use either TOP or MAX to get the value you want. Usually, depending on your indexes, TOP is the better approach. Just remember that TOP needs an ORDER BY statement. You can make each table a derived table and then join the two derived tables.
SELECT ....
FROM (SELECT TOP 1 a.Col1, a.Col2
FROM Tab1 a
ORDER BY a.updatedt DESC) AS a
JOIN (SELECT TOP 1 b.Col1, b.Col2
FROM Tab2 b
ORDER BY b.updatedt DESC) AS b
ON b.Col1 = a.Col1
Granted your code is going to look radically different, but you get the idea.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply