How to add conditions for oracle deduplication

Oracle's methods to remove duplicate records are: DISTINCT: Remove all duplicate rows with the same column value. GROUP BY: Group rows of the same value and return only one representative row. ROW_NUMBER(): Assign a unique integer to each row to identify and delete duplicate rows. You can also add conditions to remove only duplicate rows that meet certain conditions, such as the salary value greater than 5000.

Oracle to remove duplicate records and add conditions

Oracle provides several ways to remove duplicate records, including DISTINCT, GROUP BY, and ROW_NUMBER().

Using DISTINCT

The DISTINCT operator removes duplicate rows from the result set. It will judge the repeatability based on the values ​​of all columns.

 <code class="sql">SELECT DISTINCT column1, column2 FROM table_name;</code>

Using GROUP BY

The GROUP BY operator groups rows with the same value and returns only one representative row in each group.

 <code class="sql">SELECT column1, column2 FROM table_name GROUP BY column1, column2;</code>

Use ROW_NUMBER()

The ROW_NUMBER() function assigns a unique integer to each row in the table. We can use this value to identify duplicate rows and delete them.

 <code class="sql">WITH CTE AS ( SELECT column1, column2, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS row_num FROM table_name ) SELECT column1, column2 FROM CTE WHERE row_num = 1;</code>

Add conditions

Conditions can be added in the above method to remove only duplicate rows that meet the specific conditions. For example, the following query uses DISTINCT to remove duplicate rows with a salary column value greater than 5000:

 <code class="sql">SELECT DISTINCT column1, column2 FROM table_name WHERE salary > 5000;</code>

Similarly, we can use the GROUP BY and HAVING clauses to remove duplicate groups that meet certain conditions:

 <code class="sql">SELECT column1, column2 FROM table_name GROUP BY column1, column2 HAVING COUNT(*) > 1 AND SUM(salary) > 10000;</code>

The above is the detailed content of How to add conditions for oracle deduplication. For more information, please follow other related articles on the PHP Chinese website!