Several methods of oracel query to delete duplicate records

Build a Table statement
CREATE TABLE Persons
(
PersonID int,
LastName varchar (255),
FirstName varchar (255),
Address varchar (255),
City varchar (255)
);

INSERT into Persons values (1, ' A ', ' AA ', ' aaa ', ' aaaa ');
INSERT into Persons values (1, ' A ', ' AA ', ' aaa ', ' aaaa ');
INSERT into Persons values (1, ' A ', ' AA ', ' aaa ', ' aaaa ');
INSERT into Persons values (1, ' A ', ' AA ', ' aaa ', ' aaaa ');
INSERT into Persons values (2, ' a ', ' AA ', ' aaa ', ' aaaa ');
INSERT into Persons values (2, ' a ', ' AA ', ' aaa ', ' aaaa ');
INSERT into Persons values (3, ' A ', ' AA ', ' aaa ', ' aaaa ');
INSERT into Persons values (3, ' A ', ' AA ', ' aaa ', ' aaaa ');
INSERT into Persons values (4, ' A ', ' AA ', ' aaa ', ' aaaa ');
INSERT into Persons values (5, ' a ', ' AA ', ' aaa ', ' aaaa ');

SELECT * from Persons order by PersonID;
PERSONID LASTNAME FIRSTNAME ADDRESS City
1 1 a AA AAA AAAA
2 1 a AA AAA AAAA
3 1 a AA AAA AAAA
4 1 a AA AAA AAAA
5 2 a AA AAA AAAA
6 2 a AA AAA AAAA
7 3 a AA AAA AAAA
8 3 a AA AAA AAAA
9 4 a AA AAA AAAA
5 a AA AAA AAAA


1, look for redundant records in the table, repeat records are based on a single field (PersonID) to judge.
SELECT * from Persons where PersonID in (select PersonID from Persons Group by PersonID have (count (PersonID)) >1) ;
order by PersonID;

PERSONID LASTNAME FIRSTNAME ADDRESS City
1 a AA AAA AAAA
1 a AA AAA AAAA
1 a AA AAA AAAA
1 a AA AAA AAAA
2 a AA AAA AAAA
2 a AA AAA AAAA
3 a AA AAA AAAA
3 a AA AAA AAAA


2, delete redundant records in the table, repeat records are based on a single field (PersonID) to judge, leaving only the smallest ROWID records.
SELECT * from Persons
where PersonID in (select PersonID from Persons GROUP by PersonID have count (PersonID) > 1)
and rowID not in (select min (rowid) from Persons GROUP by PersonID have Count (PersonID) >1)
order by PersonID;

Delete from Persons
where PersonID in (select PersonID from Persons GROUP by PersonID have count (PersonID) > 1)
and rowID not in (select min (rowid) from Persons GROUP by PersonID have Count (PersonID) >1)

PERSONID LASTNAME FIRSTNAME ADDRESS City
1 a AA AAA AAAA
1 a AA AAA AAAA
1 a AA AAA AAAA
2 a AA AAA AAAA
3 a AA AAA AAAA


3. Find redundant duplicate records (multiple fields) in the table
SELECT * from Persons a
where (a.personid,a.city) in (select Personid,city from Persons GROUP by Personid,city have count (*) > 1)
order by PersonID;

PERSONID LASTNAME FIRSTNAME ADDRESS City
1 a AA AAA AAAA
1 a AA AAA AAAA
1 a AA AAA AAAA
1 a AA AAA AAAA
2 a AA AAA AAAA
2 a AA AAA AAAA
3 a AA AAA AAAA
3 a AA AAA AAAA


4. Delete extra duplicate records (multiple fields) in the table, leaving only the record with ROWID minimum
SELECT * from Persons a
where (a.personid,a.city) in (select Personid,city from Persons GROUP by Personid,city have count (*) > 1)
and rowID not in (select min (rowid) from Persons GROUP by Personid,city have Count (*) >1)
order by PersonID;

Delete from Persons a
where (a.personid,a.city) in (select Personid,city from Persons GROUP by Personid,city have count (*) > 1)
and rowID not in (select min (rowid) from Persons GROUP by Personid,city have Count (*) >1)

PERSONID LASTNAME FIRSTNAME ADDRESS City
1 a AA AAA AAAA
1 a AA AAA AAAA
1 a AA AAA AAAA
2 a AA AAA AAAA
3 a AA AAA AAAA


5. Find redundant duplicate records (multiple fields) in the table, not including the smallest ROWID records
SELECT * from Persons a
where (a.personid,a.city) in (select Personid,city from Persons GROUP by Personid,city have count (*) > 1)
and rowID not in (select min (rowid) from Persons GROUP by Personid,city have Count (*) >1)
and rowID not in (select min (rowid) from Persons GROUP by PERSONID,SEQ have Count (*) >1)
order by PersonID;

PERSONID LASTNAME FIRSTNAME ADDRESS City
1 a AA AAA AAAA
1 a AA AAA AAAA
1 a AA AAA AAAA
2 a AA AAA AAAA
3 a AA AAA AAAA

Several methods of oracel query to delete duplicate records