Brilliant!! never thought to use PARTITION BY 🙂

Here's what it looks like ...

A big thank you for your time.

SELECT a.custno

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

, company

, RTRIM(address1) AS address1

, add1.zip

, MAX(a.custno) OVER( PARTITION BY RTRIM(a.firstname) + RTRIM(a.lastname) + RTRIM(add1.zip)

) AS keepno

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