1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Best way to eliminate duplicate records from a table.,?!

Discussion in 'SQL PL/SQL' started by Vicky, Jun 18, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi frndz,

    Could you tell me the best way to eliminate duplicate records from a table?!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Candidate solutions can be a little...
    the best option can be defined proceeding from characteristics of data: volume, data types, etc.


    !!! it is necessary to define the rule by which duplicate records will be excluded from selection !!!

    Code (SQL):

    DROP TABLE test_tab purge ;
    CREATE TABLE test_tab
    (id INT PRIMARY KEY,
     x INT,
     y varchar2(3)
    );
    INSERT INTO test_tab
    SELECT
        level,
        MOD(level,1000),
        dbms_random.string('U',3)        
    FROM dual
    CONNECT BY level <= 10000;

    rem USE GROUP BY
    SELECT   x,y,MAX(id) keep (dense_rank FIRST ORDER BY x) id  FROM test_tab GROUP BY x,y;

    rem USE analityc FUNCTION
    SELECT x,y,id FROM
    (SELECT x,y ,id ,ROW_NUMBER() OVER  (partition BY x,y ORDER BY x) rn FROM test_tab
    )
    WHERE rn = 1;

     
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    If your table has been properly designed, it should be having at least a Primary Key which would technically prevent it from having duplicates.
    However, if there are "duplicates" based on other rules (for ex. , no 2 records may have same value for column x, y and z) then you may enforce them with unique keys/indexes.

    If these constraints are not there, then as Sergey mentioned, it's necessary to know how YOU define the criteria for duplicate records and use it to remove the duplicates.

    You can refer to this article which explores various ways to do it:
    http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table