How to delete only one repeat in Oracle

SQL statement for querying and deleting duplicate records
1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (Id ).
Select * from table where Id in (select Id from Table group by Id having count (Id)> 1)

2. Delete unnecessary duplicate records in the Table. duplicate records are determined based on a single field (Id), leaving only the records with the smallest rowid
DELETE from table WHERE (id) IN (SELECT id FROM Table group by id having count (id)> 1) and rowid not in (select min (ROWID) FROM table group by id having count (*)> 1 );

3. Search for redundant duplicate records in the table (multiple fields)
Select * from Table
Where (a. Id, a. seq) in (select Id, seq from Table group by Id, seq having count (*)> 1)
4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid
Delete from Table
Where (a. Id, a. seq) in (select Id, seq from Table group by Id, seq having count (*)> 1)
And rowid not in (select min (rowid) from Table group by Id, seq having count (*)> 1)
5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid
Select * from Table
Where (a. Id, a. seq) in (select Id, seq from Table group by Id, seq having count (*)> 1)
And rowid not in (select min (rowid) from Table group by Id, seq having count (*)> 1)

(2)
For example
There is A field "name" in Table ",
The "name" value may be the same for different records,
Now, you need to query items with duplicate "name" values between records in the table;
Select Name, Count (*) From A Group By Name Having Count (*)> 1
If the gender is also the same, the statement is as follows:
Select Name, sex, Count (*) From A Group By Name, sex Having Count (*)> 1
(3)
Method 1
Copy codeThe Code is as follows: declare @ max integer, @ id integer
Declare cur_rows cursor local for select Main field, count (*) from table name group by main field having count (*)>; 1
Open cur_rows
Fetch cur_rows into @ id, @ max
While @ fetch_status = 0
Begin
Select @ max = @ max-1
Set rowcount @ max
Delete from table name where primary field = @ id
Fetch cur_rows into @ id, @ max
End
Close cur_rows

Set rowcount 0 method 2
"Repeat record" has two duplicate records. One is a completely repeated record, that is, a record with all fields already exists. The other is a record with duplicate key fields, for example, the Name field is repeated, while other fields are not necessarily repeated or can be ignored.
1. For the first type of repetition, it is easier to solve.
Select distinct * from tableName
You can get the result set without repeated records.
If the table needs to delete duplicate records (one record is retained), you can delete the record as follows:Copy codeThe Code is as follows: select distinct * into # Tmp from tableName
Drop table tableName
Select * into tableName from # Tmp
Drop table # Tmp

The reason for this repetition is that the table design is not weekly. You can add a unique index column.
2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:
Assume that the duplicate fields are Name and Address. You must obtain the unique result set of the two fields.Copy codeThe Code is as follows: select identity (int, 1, 1) as autoID, * into # Tmp from tableName
Select min (autoID) as autoID into # Tmp2 from # Tmp group by Name, autoID
Select * from # Tmp where autoID in (select autoID from # tmp2)

The last select command gets the result set with no duplicate Name and Address (but an autoID field is added, which can be omitted in the select clause when writing)
(4)
Duplicate Query
Copy codeThe Code is as follows: select * from tablename where id in (
Select id from tablename
Group by id
Having count (id)> 1
)