A duplicate element in a deleted table in Oracle retains one of the items

I remember a colleague asked me this, said the interview before the problem encountered, the following I introduce three ways.

First we create a test table here to add the appropriate test data.

CREATE TABLE Test (ID number,name varchar (10));
INSERT into test values (1, ' Liufang ');
INSERT into test values (2, ' Xiaozhang ');
INSERT into test values (3, ' Dawei ');
INSERT into test values (4, ' Laotan ');
INSERT into test values (5, ' Laotan ');
INSERT into test values (6, ' Laotan ');
INSERT into test values (7, ' Dawei ');

1, the eradication of the name of the same ID different from the way to judge (ID must be unique)

Delete from Test a where exists (select null from Test B where b.name=a.name and b.id>a.id);

2, with ROWID to replace the ID, more suitable than the above method, no field unique restrictions

Delete from Test a where exists (select null from Test B where b.name=a.name and B.rowid>a.rowid);

3, by the analysis function according to the name group to generate the sequence number, and then delete the number greater than 1 data

(Note: Rder by 2,3 a query based on the second third word of the query orderby order)

Select rowID as Rid,name,row_number () over (partition by name order by ID) from test order by 2, 3;

The analysis function will record the number of occurrences of the same name, and delete the corresponding number of data greater than 1.

Analytic functions: Row_number () over (partition by name) simply say Row_number (), starting with 1, returns a number for each grouped record,

The Row_number () over here (partition by name) is the Name column ascending, and then the No name record in descending order returns an ordinal number.

Delete from test
where rowID in (select rowID
From (select rowID as RIDs,
Row_number () over (partition by name, order by ID) as SEQ
From Test)
where seq > 1);

A duplicate element in a deleted table in Oracle retains one of the items