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