here's an example :

SELECT a.custno

, RTRIM(a.firstname) + ' ' + RTRIM(a.lastname) AS name

, company

, RTRIM(address1) AS address1

, add1.zip

, ROW_NUMBER() OVER( PARTITION BY a.CustNo ORDER BY

--just a simple sum based on amount of data populated

(CASE WHEN RTRIM(a.firstname) + ' ' + RTRIM(a.lastname) IS NOT NULL THEN 1 ELSE 0 END

+ CASE WHEN company IS NOT NULL THEN 1 ELSE 0 END

+ CASE WHEN RTRIM(address1) IS NOT NULL THEN 1 ELSE 0 END

+ CASE WHEN add1.zip IS NOT NULL THEN 1 ELSE 0 END ) Desc

) AS DupeOrder

FROM cusfil a

JOIN address add1 ON a.custno = add1.custno

WHERE RTRIM(a.firstname) + RTRIM(a.lastname) + RTRIM(add1.zip) IN (

SELECT RTRIM(b.firstname) + RTRIM(b.lastname) + RTRIM(add2.zip)

FROM cusfil b

JOIN address add2 ON b.custno = add2.custno

WHERE add2.curraddr = 1

AND RTRIM(b.lastname) <> ''

GROUP BY b.firstname

, b.lastname

, add2.zip

HAVING COUNT(b.firstname + b.lastname + add2.zip) > 1)

AND add1.curraddr = 1

AND RTRIM(a.lastname) <> ''

ORDER BY a.lastname

, a.firstname

, add1.zip