Is it possible to convert this Outer apply to Left Join?

Question

SELECT
    hb.custnum,hb.custname as Cname,dv.custname as [CustName],'0' + hb.cellnum as cellnum,dv.ccelnumber,
    UPPER(hb.subd) as subd,UPPER(dv.BilStAdd) as BilStAdd,hb.entity,dv.BilCtAdd
FROM [cust] as dv
OUTER APPLY (
    SELECT TOP 1 * FROM ##custnumtbl WHERE ##custnumtbl.CustNum = dv.custnum ORDER BY totaldue DESC
) hb
Where 
    '0' + hb.cellnum <> dv.ccelnumber    
    or RTRIM(LTRIM(hb.custname)) <> RTRIM(LTRIM(dv.CustName)) 
    or UPPER(hb.subd) <> RTRIM(LTRIM(UPPER(dv.BilStAdd)))
    or UPPER(hb.entity) <> RTRIM(LTRIM(UPPER(dv.BilCtAdd)))

Answer 1

(NOTE: OP identified the database after this answer was written. It does not apply to SQL Server 2000.)

Yes, if you want. The easiest way is to use a window function, such as row_number():

SELECT hb.custnum,hb.custname as Cname, dv.custname as [CustName],
       '0' + hb.cellnum as cellnum, dv.ccelnumber,
       UPPER(hb.subd) as subd,UPPER(dv.BilStAdd) as BilStAdd,hb.entity,dv.BilCtAdd
FROM [cust] dv LEFT JOIN
     (SELECT c.*,
             ROW_NUMBER() OVER (PARTITION BY c.CustNum ORDER BY totaldue DESC) as seqnum
      FROM ##custnumtbl c
     )  hb
     ON hb.CustNum = dv.custnum AND seqnum = 1
WHERE '0' + hb.cellnum <> dv.ccelnumber  or
      RTRIM(LTRIM(hb.custname)) <> RTRIM(LTRIM(dv.CustName)) or
      UPPER(hb.subd) <> RTRIM(LTRIM(UPPER(dv.BilStAdd))) or
      UPPER(hb.entity) <> RTRIM(LTRIM(UPPER(dv.BilCtAdd)));

I think the outer apply is likely to have better performance.

Answer 2

For SQL Server 2000:

SELECT
    hb.custnum,hb.custname as Cname,dv.custname as [CustName],'0' + hb.cellnum as cellnum,dv.ccelnumber,
    UPPER(hb.subd) as subd,UPPER(dv.BilStAdd) as BilStAdd,hb.entity,dv.BilCtAdd
FROM [cust] as dv
LEFT JOIN(  
    SELECT b.*
    FROM (
        SELECT CustNum, totaldue = MAX(totaldue)
        FROM ##custnumtbl
        GROUP BY CustNum
    )a
    INNER JOIN ##custnumtbl b
        ON b.CustNum = a.CustNum
        AND b.totaldue = a.totaldue
)hb
    ON hb.CustNUm = dv.custnum
Where 
    '0' + hb.cellnum <> dv.ccelnumber    
    or RTRIM(LTRIM(hb.custname)) <> RTRIM(LTRIM(dv.CustName)) 
    or UPPER(hb.subd) <> RTRIM(LTRIM(UPPER(dv.BilStAdd)))
    or UPPER(hb.entity) <> RTRIM(LTRIM(UPPER(dv.BilCtAdd)))

As pointed by M.Ali, if you're not using a case-sensitive collation, you may want to get rid of the UPPER.