ChrisM@home (10/14/2012)

---

Don. (10/12/2012)

---

I'd like to get the join the tables, but only show the most recent record in History.

Why HR1? HR3 is on the most recent row. Assuming you mean HR3, then this works against the sample data set;

SELECT c.*, h.*

FROM #Contact c

INNER JOIN (

SELECT h1.Accountno, h1.LastDate, RECID = MIN(h1.RECID)

FROM #History h1

INNER JOIN (

SELECT Accountno, MAX(LastDate) LastDate

FROM #History

GROUP BY Accountno

) l

ON l.Accountno = h1.Accountno

AND l.LastDate = h1.LastDate

GROUP BY h1.Accountno, h1.LastDate

) h

ON h.Accountno = c.Accountno