CODEWITHSUNDEEP
sqloracle-databaseduplicatesrowsanalytical
Jeby Sebastian
Jeby Sebastian

Reputation: 41

Delete duplicate rows using Analytical functions

Can we delete duplicate rows using analytical functions? I mean using row_number() or rank or dense_rank() in Sql query in Oracle?

Upvotes: 0

Views: 29525

Answers (8)

Prashant Awasthi
Prashant Awasthi

Reputation: 1

DELETE FROM emp 
WHERE rowid IN
(
    SELECT rid from
    (
        SELECT rowid AS rid,
        DENSE_RANK() OVER(PARTITION BY empno ORDER BY rowid) AS rn
        FROM emp
    )
    WHERE rn > 1
);

Here you can use both RANK() and DENSE_RANK() since both will give unique records when order by rowid.

Upvotes: 0

Sebastian Thomas
Sebastian Thomas

Reputation: 1

Suppose we want to remove duplicate sal from emp table:

select * from (
  select 
    empno, 
    ename, 
    sal, 
    row_number() over (partition by sal order by sal asc) rw_number
  from emp)
where rw_number = 1

Upvotes: 0

user14186286
user14186286

Reputation: 1

You can delete the duplicate rows using this query:

DELETE FROM EMP
WHERE ROWID IN
(
SELECT RID FROM
(SELECT ROWID RID,ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ROWID) R FROM EMP) 
WHERE R>1
);

Upvotes: 0

Yamini Sirisha
Yamini Sirisha

Reputation: 1

This is the sort of thing you should do:

DELETE  FROM emp
WHERE   rowid IN (
        SELECT  rid
        FROM    (
                  SELECT    rowid rid ,
                            DENSE_RANK() OVER ( PARTITION BY empno ORDER BY rowid ) rn
                  FROM      emp
                ) AS RR
        WHERE   rn > 1 );

Upvotes: -1

Baskar A
Baskar A

Reputation: 11

follow these steps to delete duplicate records without using rowid,rownum 1. create table to store distinct values of original table.

    create table newtable as select distinct * from tablename;

  1. delete the records from original table.

     truncate table tablename;

  2. insert a temporary table values into original table.

     insert into tablename select * from newtable;

Upvotes: 1

JWK
JWK

Reputation: 492

You can use ROW_NUMBER() over a partition of columns that should be unique for you, e.g: ROW_NUMBER() OVER (PARTITION BY COLUMN1, COLUMN2 ORDER BY COLUMN1). Every result that has a rownumber > 1 is a duplicate.

You can then for example return the rowid's for those and delete them.

Upvotes: 3

Aspirant
Aspirant

Reputation: 2278

We can use Analytical Functions or any other way like Aggregate Functions , 'DISTINCT` to find out whether we have duplicates in our table or not

like using Analytical Functions

select col1,col2,col3 from (select tbl.*,rowid rid,row_number() over (partition by col1,col2,col3 order by col1) rnum
from tbl) where rnum>1;

but to delete them we need to get the ROWID of those whose rnum>1

delete from tbl where rowid in 
(select rid from (select tbl.*,rowid rid,row_number() over (partition by col1,col2,col3 order by col1) rnum
  from tbl) where rnum>1)

or else we can simply use

 create new_table as select distinct * from old_table

Upvotes: 1

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

Yes. But it could be made a bit simpler with aggregate function

-- delete duplicates
delete from table t1
where t1.rowid not in (
   select min(t2.rowid)
   from table t2
   group by t2.col1, t2.col2, ...
)

fiddle

Upvotes: 1

Related Questions