If the records in history have the same date without a timestamp there needs to be a unique way to identify the latest record. If RecID is sequential then you don't even need the date.

From your dummy data and what you're looking for (H1, H4) it looks as if the RecID is sequential but in decending order which I'm assuming it's not.

Here's a modified script of the one above using the RecID.

CREATE TABLE #CONTACT

(

ACCOUNTNOVARCHAR(5),

COMPANYVARCHAR(50),

CONTACTVARCHAR(50),

RECIDVARCHAR(5)

)

CREATE TABLE #HISTORY

(

ACCOUNTNOVARCHAR(5),

LASTUSERVARCHAR(8),

LASTDATEDATE,

RECIDVARCHAR(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'

--Assuming that the RecID is sequential and ascending

select C.*, H.*

from #CONTACT C

left join (

select Hi.*

from #HISTORY Hi

inner join (

select max(recid) Min_RecID, ACCOUNTNO

from #HISTORY

group by ACCOUNTNO

) S

on Hi.ACCOUNTNO = S.ACCOUNTNO

and Hi.RECID = S.Min_RecID

) H

on C.ACCOUNTNO = H.ACCOUNTNO

--This works to get H1 and H4 but it implies that the recid is in descending order...

select C.*, H.*

from #CONTACT C

left join (

select Hi.*

from #HISTORY Hi

inner join (

select min(recid) Min_RecID, ACCOUNTNO

from #HISTORY

group by ACCOUNTNO

) S

on Hi.ACCOUNTNO = S.ACCOUNTNO

and Hi.RECID = S.Min_RecID

) H

on C.ACCOUNTNO = H.ACCOUNTNO

drop table #Contact

drop table #History