Thanks much for the info. I tried both versions on production data and found the row_number to be much faster, which took a second to run compared to 14 seconds to run using cross apply.
Account table has 121,000 records and call has 651,000 records. Due to account type clause there are only 8,000 acount and 98,000 call records affected. I wonder why the results are so different, acctid is primary key and index does exist on callactid.
14 seconds
select acctservareaid, acctid, acctname, acctstorenumber, callconfirmation, callaccttypeid, work_order
from account with (nolock)
cross apply (
select top 1 calltime, callconfirmation, callaccttypeid, callcokeagent + callpo work_order
from call with (nolock)
where callacctid = acctid
and callaccttypeid = 'c'
order by calltime desc) as call
where accttypeid = 'c'
1 second
with cteCall
as
(
select acctservareaid, acctid, acctname, acctstorenumber, calltime, callconfirmation, callaccttypeid, callcokeagent + callpo work_order
,row_number() over (partition by AcctID order by CallTime desc ) as RowN
from call with (nolock)
inner join account with (nolock) on callacctid = acctid
and callaccttypeid = 'c'
and accttypeid = 'c'
)
Select * from cteCall
where RowN <=1
order by AcctID, CallTime Desc