ChrisM@home (10/17/2012)
Eric M Russell (10/15/2012)
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
If Don has time, it would be interesting to know how this triangular join method compares to the aggregate methods. With small partitions it could be quite performant; with large partitions it won't. TJ's scale poorly.
I agree the 2000 method would be potentially problematic in terms of optimization when compared to the the 2005+ windowing function method, although both of them could take considerable resources when dealing with million+ row tables. It's essential that HISTORY table be indexed in a way that supports it, perhaps even a covered indexed just to support this particular report, if it's called multiple times daily.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho