WITH Ranks
AS
(
	SELECT *
		,RANK() OVER (PARTITION BY AddressBarcode ORDER BY MemberTypeID) AS rn
	FROM Member
)
-- Check those with a rn greater than 1 are the ones you want to delete.
select * from Ranks;
-- If happy, remove the above select and uncomment the following.
--DELETE Ranks
--WHERE rn > 1;

quote:

---

Originally posted by Ifor

Some consumable test data would be useful.

---

ID	AddressBarcode	MemberTypeID
4196386	1301010220000221301220333113213312213	2
4050437	1301010220000221301220333113213312213	1
4171374	1301010220001221100121310022320122313	2
4143468	1301010220001221102122323233210023113	2
14116602	1301010220102112020201312230222023013	3
4116602	1301010220102112020201312230222023013	2
4160218	1301010220210201302010332210230303013	1
14136832	1301010220210201302002321221220110213	1
19092654	1301010220221201100021321322122113313	2
4130282	1301010222000001023001332230300100113	2
4088587	1301010222000001221030311031020033213	2
2425916	1301010222000011222002313033131313313	2
12425916	1301010222000011222002313033131313313	3
4050798	1301010222000100112100312312001120313	1
14186560	1301011000000002212012331020223020313	3
19124174	1301011000000022122000321121133111313	2
4077523	1301011000000030001000333103311133213	1
19164525	1301011000000111212200330302211320313	3
19164524	1301011000000111212200330302211320313	2

-- *** Test Data ***
CREATE TABLE #t
(
	ID int NOT NULL
	,AddressBarcode char(37) NOT NULL
	,MemberTypeID tinyint NOT NULL
);
INSERT INTO #t
VALUES(4196386,'1301010220000221301220333113213312213', 2)
	,(4050437,'1301010220000221301220333113213312213', 1)
	,(4171374,'1301010220001221100121310022320122313', 2)
	,(4143468,'1301010220001221102122323233210023113', 2)
	,(14116602,'1301010220102112020201312230222023013', 3)
	,(4116602,'1301010220102112020201312230222023013', 2)
	,(4160218,'1301010220210201302010332210230303013', 1)
	,(14136832,'1301010220210201302002321221220110213', 1)
	,(19092654,'1301010220221201100021321322122113313', 2)
	,(4130282,'1301010222000001023001332230300100113', 2)
	,(4088587,'1301010222000001221030311031020033213', 2)
	,(2425916,'1301010222000011222002313033131313313', 2)
	,(12425916,'1301010222000011222002313033131313313', 3)
	,(4050798,'1301010222000100112100312312001120313', 1)
	,(14186560,'1301011000000002212012331020223020313', 3)
	,(19124174,'1301011000000022122000321121133111313', 2)
	,(4077523,'1301011000000030001000333103311133213', 1)
	,(19164525,'1301011000000111212200330302211320313', 3)
	,(19164524,'1301011000000111212200330302211320313', 2);
-- *** End Test Data ***

select * from #t;

WITH Ranks
AS
(
	SELECT *
		,RANK() OVER (PARTITION BY AddressBarcode ORDER BY MemberTypeID) AS rn
	FROM #t
)
DELETE Ranks
WHERE rn > 1;

select * from #t;

WITH Ranks
AS
(
	SELECT *
		,ROW_NUMBER() OVER (PARTITION BY AddressBarcode ORDER BY MemberTypeID, ID) AS rn
	FROM #t
)
DELETE Ranks
WHERE rn > 1;

SELECT
         vwPersons.ID,
         vwPersons.AddressBarcode,
         vwPersons.MemberTypeID
         
FROM
        vwPersons

WHERE
   Blah
   AND Blah
Order by NewID()