Delete duplicate records using rownum in sql

Question

Please help me to know delete records based on the rownum where even id is duplicate it won't happen but if it is the case.

select rownum,a.* from a;

    ROWNUM         ID NAME
---------- ---------- ----------
         1          1 leo_1
         2          2 leo_2
         3          3 leo_3
         4          1 leo_1
         5          2 leo_2
         6          3 leo_3

Query Tried but deletes all 6 rows.

DELETE FROM a
WHERE rownum not in
(SELECT MIN(rownum)
FROM a
GROUP BY name);

But this Query gives correct result:

SELECT MIN(rownum)
FROM a
GROUP BY name

    ROWNUM
----------
         1
         2
         3

Expected Result :

    ROWNUM         ID NAME
---------- ---------- ----------
         4          1 leo_1
         5          2 leo_2
         6          3 leo_3

Expected result is result of which query? – tilda Jan 11 '14 at 20:39 — tilda, Jan 11 '14 at 20:39

score 7 · Accepted Answer · answered Jan 11 '14 at 20:05

Use the rowid

DELETE FROM table_name a
 WHERE EXISTS( SELECT 1
                 FROM table_name b
                WHERE a.id = b.id
                  AND a.name = b.name
                  AND a.rowid > b.rowid )

Of course, you could do a.rowid < b.rowid as well. The rowid is just the physical address of the row so it doesn't matter whether you delete the row that has the larger or the smaller address.

Your expected results, though, don't make sense.

Expected Result :

        ROWNUM         ID NAME
    ---------- ---------- ----------
             4          1 leo_1
             5          2 leo_2
             6          3 leo_3

The rownum of a result set is always assigned at query time. That means that a particular row may appear with different rownum values in different queries (or when the same query is run multiple times). rownum is always sequential so you can never have a rownum of 4 in a result set without also having rownum values of 1, 2, and 3 in the same result set. Whichever duplicate row you delete, your result will be

Expected Result :

    ROWNUM         ID NAME
---------- ---------- ----------
         1          1 leo_1
         2          2 leo_2
         3          3 leo_3

But the rownum values are arbitrary. It would be just as valid for Oracle to return

Expected Result :

    ROWNUM         ID NAME
---------- ---------- ----------
         1          2 leo_2
         2          3 leo_3
         3          1 leo_1

Sai · Answer 2 · 2014-02-02T04:49:47.323

2

DELETE FROM a
WHERE rowid not in
(SELECT MIN(rowid) FROM a group BY name);

edited Feb 02 '14 at 04:49

answered Jan 12 '14 at 07:27

Sai

659
4
12
21

1

Row num assigned to each row at the time query execution.. Rownum is temporary but rowid is permanet – Sai Jan 12 '14 at 07:33
and if someone else designed this without a RowId? – Gary Nov 02 '17 at 13:47

score 2 · Answer 3 · answered Aug 31 '15 at 09:08

2

delete from tb_test where c1 in (select c1 from (select c1,c2, 
row_number() over (partition by c2 order by c2)  rn
from tb_test) a where a.rn >1 );

C1 is the primary key column and c2 is the column with duplicate value.

answered Aug 31 '15 at 09:08

sabya

109
2
4
20

Delete duplicate records using rownum in sql

3 Answers3

Linked