DiverKas (10/12/2012)
For older environments or other than T-SQL flavor, more generically would be something like (untested):
SELECT c.Accountno,C.Company,C.Contact,C.Recid,H.lastuser,H.lastdate
FROM contact C
LEFT JOIN history h ON C.accountno=h.accountno
LEFT JOIN (SELECT accountno,MAX(lastdate) AS lastdate
FROM history
GROUP BY accountno) mx ON h.accountno=mx.accountno
AND h.lastdate=mx.lastdate
Thanks for the reply, unfortunately it doesnt work. 🙁
Since h.accountno returns 1 of each accountno, its not limiting the h.accountno results to only one record per accountno and all history records are being returned.
If we could do that with the recid field, it should work, although I currently have no clue how to do that.