Comparing Dates in SQL

I am wondering how I would create a SELECT that will select the most recent date from one of two tables. For example, table1 has a field called LastUpdate and table 2 has a field called LastUpdate. I need to grab only the most recent date. I tried this using an inner join...but that didn't work because it only picks the lastupdate form one table only. talbe1 and table2 are tied by table2.table1id.

Can anyone help?

you could do a MAX(lastupdatedate) and then do the INNER JOIN.
SELECT
table1.column
FROM
table1
INNER JOIN table2
on table1.table1id = able2.table1id
WHERE
MAX(table1.lastupdatedate) = MAX(table2.lastupdatedate)|||I'm sorry I don't understand how this works. If the last update date is today in table2, I want to return that date. Otherwise I want to return the last update date in table1. Does this mak sense?|||SELECTCASE WHEN (table1.LastUpdate > table2.LastUpdate) THEN table1.LastUpdate ELSE table2.LastUpdate END AS MaxLastUpdate
FROM ... usual join statement|||

This is almost perfect! Thanks!

Followup: what if there is no instance of table2.table1id? Then I get nothing returned but in reality I still want table1.lastupdate returned.

Thanks again!

|||Ok I'm an idiot. I just switched the when statement and the then and else so the else displays the main from table1 and walaa. Thank you so much for this. I will get better at these case statements yet!|||

You have to use OUTER JOIN to make sure all rows are included. For example (using my own dummy master/detail tables):

SELECT A.ID_MASTER, CASE WHEN (A.DateEntered > ISNULL(B.DateEntered, '01-01-1900'))
THEN A.DateEntered ELSE b.DateEntered END AS MaxLastUpdate
FROM TestDate AS A LEFT OUTER JOIN
TestDate2 AS B ON A.ID_MASTER = B.ID_MASTER

ISNULL() function is used to make sure NULL date value (for the missing row in Detail) defaults to "01-01-1900", assuming that date will be small enough. Also, this query will return multiple rows if there are more than one Detail rows for a given Master row. Depending on your situation, you may or may not have to change this.

|||

SELECT keyid,MAX(DateEntered)
FROM (
SELECT keyid,DateEntered FROM table1
UNION
SELECT keyid,DateEntered FROM table2
) z
GROUP BY keyid

Would work as well, and only return a single result for each keyid.