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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho