SELECT c.*, h.*
FROM Contact c
OUTER APPLY (
	SELECT TOP 1 h.*
	FROM History h 
	WHERE h.Accountno = c.Accountno
	ORDER BY h.LastDate DESC
) x

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

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

SELECT accountno,MAX(lastdate) AS lastdate
FROM history
GROUP BY accountno

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 and h.RECID IN (....

select h.*, c.* 
   from Contact c 
   inner join (
     select top 1 h1.* from History h1
     inner join (select Accountno, max(LastDate) LastDate from History group by Accountno) L
     on h1.Accountno = L.Accountno and h1.LastDate = L.LastDate) h
  on h.Accountno = c.Accountno

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 )?

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

 CREATE TABLE #CONTACT 
        (
        ACCOUNTNO	VARCHAR(5),
        COMPANY		VARCHAR(50),
        CONTACT		VARCHAR(50),
        RECID		VARCHAR(5)
        )

 CREATE TABLE #HISTORY
        (
        ACCOUNTNO	VARCHAR(5),
        LASTUSER	VARCHAR(8),
        LASTDATE	DATE,
        RECID		VARCHAR(5)
        )

INSERT INTO #CONTACT (ACCOUNTNO, COMPANY, CONTACT, RECID)
SELECT 'C1', 'ACME', 'WILEY COYOTE', 'CR1' UNION ALL
SELECT 'C2', 'Beta Ltd', 'Joe Soap', 'CR2'

INSERT INTO #HISTORY (ACCOUNTNO, LASTUSER, LASTDATE, RECID)
SELECT 'C1', 'JOE', '20120101', 'HR1' UNION ALL
SELECT 'C1', 'BOB', '20120101', 'HR2' UNION ALL
SELECT 'C1', 'JOE', '20120201', 'HR3' UNION ALL
SELECT 'C2', 'JOE', '20120202', 'HR4' UNION ALL
SELECT 'C2', 'BOB', '20120202', 'HR5'