Don. (10/12/2012)
Chris that looks almost perfect, thank you. (Needed x instead of h in the initial select)
SELECT c.*, x.*FROM Contact c
OUTER APPLY (
SELECT TOP 1 h.*
FROM History h
WHERE h.Accountno = c.Accountno
ORDER BY h.LastDate DESC
) x
I amended my script, and tested, but our clients dont have a recent backend and they're still a ways off being upgraded. 🙁
OUTER APPLY is new to me, but looks really interesting \ useful. I'll definitely read up on it.
Any suggestions as to how Id be able to get the same results except using scripts for an older backend( SQL 2000 🙁 )?
Sorry about the late reply Don, had some issues with a drive on this lappy.
Tom's solution should work just fine. The TOP 1 may not be necessary, it's a tiebreaker in case you have more than one row with the same lastdate which also happens to be the MAX lastdate.
The following will work in cases where the business logic excludes such dupes from occurring, which may well be the case if lastdate is DATETIME - and it almost certainly is.
SELECT c.Accountno, C.Company, C.Contact, C.Recid, H.lastuser, H.lastdate
FROM contact C
LEFT JOIN (
SELECT accountno, MAX(lastdate) AS lastdate
FROM history
GROUP BY accountno
) mx ON mx.accountno = h.accountno
LEFT JOIN history h
ON h.accountno = mx.accountno
AND h.lastdate = mx.lastdate
Edit: having said that, I'd advocate Tom's approach as sound, sensible defensive coding.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]