Remove duplicate table data from Oracle

During the project process today, duplicate records exist in Oracle database tables, and duplicate data needs to be removed during display. After thinking for a long time, I finally used rank () over (partiti

During the project process today, duplicate records exist in Oracle database tables, and duplicate data needs to be removed during display. After thinking for a long time, I finally used rank () over (partiti

During the project process today, duplicate records exist in Oracle database tables, and duplicate data needs to be removed during display. After thinking for a long time, rank () over (partition by grouping field order by sorting field order)

Solved this problem.

1. First, we will introduce rank () over (partition by grouping field order by sorting field order ):

Syntax:

Rank () over (order by sorting field order)

Rank () over (partition by grouping field order by sorting field order)

1. Order: asc | desc ranking is related to the business:

2. Partition field: the partition field.

Q: What is the difference between a partition and a group?

• Partitioning only sorts the raw data (the number of records remains unchanged ),

• Grouping is used to aggregate statistics on raw data (with fewer records, one record is returned for each group ).

Note: When rank () over (order by sort field order) is used for sorting, the null value is the largest.

(If the sorting field is null, the null field may be placed at the top of the list during sorting, which affects the correctness of sorting.

Therefore, we recommend that you change dense_rank () over (order by column name sorting) to dense_rank () over (order by column name sorting nulls last)

So long as the sorting field is null, It will be placed at the end, without affecting the sorting result ).

Ii. Example

First, create a test table. f has two fields: id and no. The data in this table is:

Id no

1 11

2 22

3 22

4 33

5 55

Remove the 22 duplicate data from the no field in this table. The SQL statement is:

Select B. id, B. no from (

Select rank () over (partition BY t. no order by t. id) aa, t. * from test t) B

Where B. aa = 1

The execution result is:

Id no

1 11

2 22

4 33

5 55

Recommended reading:

How to quickly delete duplicate data in Oracle

Use a temporary table to delete Oracle duplicate data

Only one entry is left for Oracle to delete duplicate data

Opendedup 1.1.6 release, deduplication case