Remove duplicate table data from Oracle

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.