• VMware

    Learn about VMware virtualization for its products like vsphere ESX and ESXi, vCenter Server, VMware View, VMware P2V and many more

  • Linux

    Step by step configuration tutorials for many of the Linux services like DNS, DHCP, FTP, Samba4 etc including many tips and tricks in Red Hat Linux.

  • Database

    Learn installation and configuration of databases like Oracle, My SQL, Postgresql, etc including many other related tutorials in Linux.

  • Life always offers you a second chance ... Its called tomorrow !!!

    Thursday, November 24, 2016

    How to Remove Duplicate Rows from a Table

    Use rowid pseudo column. All you have to do is to keep the latest data (i.e. highest ROWID) and remove other duplicated rows.
    SELECT * FROM table1 a
    WHERE rowid < (SELECT max(rowid) FROM table1 b
    WHERE a.column1 = b.column1 AND etc...);

    OR

    create table testtt (num number);

    insert into testtt values(111);
    insert into testtt values(111);
    insert into testtt values(111);
    insert into testtt values(111);
    insert into testtt values(222);
    insert into testtt values(222);
    insert into testtt values(333);
    insert into testtt values(333);
    insert into testtt values(333);


    select * from testtt;

    delete from testtt
    where (rowid, num) not in (select max_rid, num
    from (select num,
    count(num) over (partition by num) cnt,
    max(rowid) over (partition by num) max_rid
    from testtt)
    where cnt > 1);

    select * from testtt;

    OR

    While I doubt this method has any advantages over another, it's an example:
    DELETE FROM table_a
    WHERE rowid IN
    ( SELECT rowid FROM table_a
    MINUS
    SELECT MAX( rowid ) FROM table_a
    GROUP BY column_list )

    OR

    delete from table_name where rowid not in (select max(rowid) from table group byduplicate_values_field_name);
    OR

    highest rowid does not necessarily mean latest data... since space freed from deleting rows might be reused.
    SQL> CREATE TABLE t AS SELECT level l FROM DUAL CONNECT BY LEVEL <= 5000;
    SQL> DELETE FROM t WHERE l < 5000;
    SQL> COMMIT;
    SQL> INSERT INTO t VALUES (5001);
    SQL> COMMIT;
    SQL> SELECT max(l) KEEP(DENSE_RANK LAST ORDER BY rowid) as maxrid, max(l) KEEP(DENSE_RANK FIRST ORDER BY rowid) minrid FROM t;

    MAXRID MINRID
    ---------- ----------
    5000 5001


    0 comments:

    Post a Comment