Many to One with Max Date Query?

I have a master table that has all my accounts in it.
In a 2nd table I have update notes per each account, so there are
multiple notes entries for each single ID account in teh first table.

How can I get the OLDEST dated entry in teh second table, for every
account that's had a not entered into if by joining on the ID of the
first table?

This has been driving me nuts, does that make sense to you?

Here:

Table 1 FIELDS
ACCOUNTID

TABLE 2 FIELDS
NOTEID
ACCOUNTID (Foreign Key)
DATE_ENTERED

I need to join those two tables on the ACCOUNTID, but ONLY show the most
recent date for the record I pull from Table 2, since it's got multiple
entries in table 2, it's screwing my query up. Any help/ideas?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!SELECT T1.accountid, T2.noteid, T2.date_entered
FROM Table1 AS T1
JOIN Table2 AS T2
ON T1.accountid = T2.accountid
AND T2.date_entered =
(SELECT MIN(date_entered)
FROM Table2
WHERE accountid = T2.accountid)

--
David Portas
SQL Server MVP
--