I have 3 duplicate rows in Oracle Database. Want to delete only 2nd duplicate record. How do I do?

Question

Example:

sno Empid Ename Sal
1   100   xxx   1000
2   200   yyy   2000
3   200   yyy   2000
4   200   yyy   2000

Empid,Ename,sal are columns in the EMP table. Sno is not a column,its just for understanding

How to delete 3rd record?

why don't you want to delete the 4th record as well, since that's also a duplicate? And what have you researched or tried so far? This reads like a piece of homework. — ADyson, Nov 13 '19 at 14:35
Does it matter if you delete the 2nd duplicate instead of 1st or 3rd? — Salman A, Nov 13 '19 at 14:42
If they are duplicates in a relational table then there is no difference between them and it doesn't matter which is deleted. — BriteSponge, Nov 13 '19 at 16:16
i have faced this question in an interview. want to know the possibilities — Karthika David, Nov 13 '19 at 16:47

Gordon Linoff · Answer 1 · 2019-11-13T17:43:08.127

Here is one method:

delete from example e
    where e.sno > (select distinct nth_value(e2.sno, 2) over (partition by e2.empid order by e2.sno)
                   from example e2
                   where e2.empid = e.empid  -- and other columns if necessary
                  );

This is using nth_value() to get the second value for each employee (if you want "duplicate" to mean all three columns then add conditions for them).

The select distinct is so the subquery returns only one value. Unfortunately, there is no nth_value() as an aggregation function.

EDIT:

I think you can use rowid:

delete from example e
    where e.sno > (select distinct nth_value(e2.rowid, 2) over (partition by e2.empid order by e2.rowid)
                   from example e2
                   where e2.empid = e.empid  -- and other columns if necessary
                  );

@KarthikaDavid . . . You should add a unique key to your table. — Gordon Linoff, Nov 13 '19 at 17:43

score 0 · Answer 2 · answered Nov 13 '19 at 15:20

0

This should work:

 DELETE FROM emp 
 WHERE  ROWID = (SELECT Max(ROWID) 
                 FROM (SELECT ROWID, empid 
                       FROM emp 
                       WHERE  empid = 200 
                       ORDER  BY ROWID) 
                 WHERE  ROWNUM <= 2);

answered Nov 13 '19 at 15:20

Navneet Wats

1
3

max(rowid) will return one value..then how did you filter data by rownum – Karthika David Nov 13 '19 at 16:54
the records get filtered first because where clause is executed prior to select clause. You may refer to the logical order of execution of a sql query https://stackoverflow.com/questions/4596467/order-of-execution-of-the-sql-query – Navneet Wats Nov 14 '19 at 04:44

score 0 · Answer 3 · answered Nov 13 '19 at 17:39

I think theinterviewer wanted you to find the records which have 3 or more duplicates and delete 2nd (or any from the duplicates) from it.

Try this:

Delete from emp e
Where e.rowid in 
(select rid from 
 ( select t.rowid as rid, 
          Row_number() over (partition by t.empid, t.empno, t.sal order by 1) as rn,
          Count(1) over (partition by t.empid, t.empno, t.sal order by 1) as cnt
    From emp t) 
where cnt >= 3 and rn = 2) -- rn condition is irrelvant here but kept it here for satisfaction of interviewer :)

Cheers!!

I have 3 duplicate rows in Oracle Database. Want to delete only 2nd duplicate record. How do I do?

3 Answers3