Oracle deletes a column of duplicate rows in the table

Oracle deletes a column of duplicate rows in the table

Table Name: T4

Create Table Test. T4
(
ID number (10 ),
Firstname varchar2 (50 char ),
Lastname varchar2 (50 char)
)

The ID is the primary key, and all rows with duplicate IDs are deleted (no matter whether other columns are repeated)

DELETE FROM T4 C
WHERE C.ROWID NOT IN (  SELECT MAX (A.ROWID)
FROM T4 A
GROUP BY A.ID);

Ideas:

1. group all rows and obtain the maximum rowid. The result contains the row that is not repeated and the rowid of the last row that is repeated.

  SELECT MAX (A.ROWID)
FROM T4 A
GROUP BY A.ID;

2. delete other rows in the table.

Test data:

INSERT INTO T4     VALUES (1, 'F1', 'L1');

INSERT INTO T4     VALUES (2, 'F2', 'L2');

INSERT INTO T4     VALUES (3, 'F3', 'L3');

INSERT INTO T4     VALUES (2, 'F2', 'L2');

INSERT INTO T4     VALUES (3, 'F3', 'L3');

1 F1 L1
2 F2 L2
2 F2 L2
3 F3 L3
3 F3 L3

Test results:

1 F1 L1
2 F2 L2
3 F3 L3

Another method:

DELETE FROM tb1      WHERE     tb1.c1 IN (  SELECT tb1.c1                               FROM tb1                           GROUP BY tb1.c1                             HAVING COUNT (tb1.c1) > 1)            AND tb1.ROWID NOT IN (  SELECT MIN (tb1.ROWID)                                      FROM tb1                                  GROUP BY tb1.c1                                    HAVING COUNT (tb1.c1) > 1);