How do I join tables, but select top 1 from 1-many tbl?

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

select C_RECID, H_RECID from
(
select C.RECID C_RECID, H.RECID H_RECID,
row_number() over (partition by H.ACCOUNTNO order by H.RECID desc) history_rank
from #CONTACT C
join #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO
) x
where history_rank = 1;

select C_RECID, H_RECID from
(
select C.RECID C_RECID, H.RECID H_RECID,
(select count(*) from #HISTORY HX
where HX.ACCOUNTNO = H.ACCOUNTNO and HX.RECID >= H.RECID) history_rank
from #CONTACT C
join #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO
) x
where history_rank = 1;

Eric M Russell (10/15/2012)

Don. (10/13/2012)

Hi Guys,

Unfortunately neither of those scripts worked as there are multiple identical accountno and lastdate fields. Ideally Id need to filter on the History tables Recid field.
...
...
In my example it should have returned HR3 and HR4 OR HR5.

Thanks

If running on SQL Server 2005+, the ROW_NUMBER() or RANK() function would be perfect for this.

select C_RECID, H_RECID from
(
select C.RECID C_RECID, H.RECID H_RECID, 
   row_number() over (partition by H.ACCOUNTNO order by H.RECID desc) history_rank
from #CONTACT C
join #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO
) x
where history_rank = 1;

C_RECID H_RECID
------- -------
CR1 HR3
CR2 HR5

You can simulate the functional equivalent in SQL Server 2000 less gracefully like so:

select C_RECID, H_RECID from
(
select C.RECID C_RECID, H.RECID H_RECID, 
   (select count(*) from #HISTORY HX 
      where HX.ACCOUNTNO = H.ACCOUNTNO and HX.RECID >= H.RECID) history_rank
from #CONTACT C
join #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO
) x
where history_rank = 1;

C_RECID H_RECID
------- -------
CR1 HR3
CR2 HR5

Either way, you would probably want a compound index on the HISTORY table's ACCOUNTNO and RECID columns. This would perhaps be the best suited primary key on the HISTORY table as well.

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'

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