Remove Duplicate Rows in Oracle Table

user_8997098 0
Viewed 4344

I want to do a SQL in Toad to extract all the values of a tables whits some conditions. I catch all the values but now I want add a new condition to the SQL but I don't know how. I want only catch the values that aren't duplicates. For example:

I have now this:

PRINCIPAL APELLIDO NOMBRE
a           b        c
b           c        d
c           d        e
a           l        m

In this case I want only catch the values that PRINCIPAL is not duplicated, in the example I want to catch only the second and the third row because the first and the fourth are duplicated.

toad oracle sql
4 Answers

One way is you could use this query:

SELECT principal,
    MAX(apellido) AS apellido,
    MAX(nombre) AS nombre 
FROM table_name
GROUP BY principal
HAVING COUNT(*) = 1
ORDER BY principal;
user_38444580

Have a sub-query that returns PRINCIPAL values existing more than once:

select *
from tablename
where PRINCIPAL not in (select PRINCIPAL from tablename
                        group by PRINCIPAL
                        having count(*) > 1)
user_37060160

You may use the COUNT aggregated function for the removing of all of the duplicated rows.

select PRINCIPAL, APELLIDO, NOMBRE,
count(*) over (partition by PRINCIPAL) dup_cnt
from tab

P A N    DUP_CNT
- - - ----------
a b c          2
a l m          2
b c d          1
c d e          1

The COUNT count the rows for each unique key defined in the PARTITION BY clause.

The final query selects only the unique rows, i.e. rows with DUP_CNT = 1

with dedup as (
select PRINCIPAL, APELLIDO, NOMBRE,
count(*) over (partition by PRINCIPAL) dup_cnt
from tab)
select PRINCIPAL, APELLIDO, NOMBRE
from dedup
where dup_cnt = 1

Note: using ROW_NUMBER instead of COUNT you can do deduplication, i.e. you let one of the duplicated rows in the result and remove the duplicates.

Note that this method requires a sort of the table (WINDOW SORT), which may be heavy for large tables. In this case a method using NOT EXISTS may yield better performance, as it is transformed and performed as anti hash join - HASH JOIN RIGHT ANTI.

select principal, apellido, nombre 
from tab t
where not exists 
(select null
 from tab 
 where  principal = t.principal and rowid <> t.rowid
)

Some care must be taken if the deduplication column (principal) is nullable. Contrary to the first solution with COUNT the not exusts leaves all the nulls in the result. If this is not required you must add a filter:

 and t.principal is not NULL

If you have an index on the pricipal column, the optimal execution plan looks as follows

--------------------------------------
| Id  | Operation             | Name | 
--------------------------------------
|   0 | SELECT STATEMENT      |      |    
|*  1 |  HASH JOIN RIGHT ANTI |      |     
|   2 |   INDEX FAST FULL SCAN| IDX  |  
|*  3 |   TABLE ACCESS FULL   | TAB  |  
--------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("PRINCIPAL"="T"."PRINCIPAL")
       filter(ROWID<>"T".ROWID)
   3 - filter("T"."PRINCIPAL" IS NOT NULL)
user_48081220

You can use window functions. The logic would be:

select principal, apellido, nombre
from (select t.*, count(*) over (partition by principal) as cnt
      from t
     ) t
where cnt = 1;

This will return rows where principal is NULL; such rows are eliminated by NOT EXISTS.

However, the fastest method if you have a primary key and an index on (principal, pk) is probably:

select principal, apellido, nombre
from t
where not exists (select 1
                  from t t2
                  where t2.principal = t.principal and t2.pk <> t.pk
                 );
user_11440350
Related