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!

how to identify duplicate records in a table

Discussion in 'SQL PL/SQL' started by neelima315@gmail.com, Jul 14, 2014.

  1. Hi Team,

    can you please suggect me on how to get a duplicate records present in a table
     
  2. rshpiler

    rshpiler Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    75
    Hi,
    You can use group by query using having count(*) > 1:

    Select key_column1, key_column2,... key_columnN, count(*)
    from table_name
    group by key_column1, key_column2,... key_columnN
    having count(*) > 1

    Good Luck!
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Use analytic function : row_number / rank /dense_rank

    simple example (the variant solution will depend on what look it is necessary to receive result rows):

    Code (SQL):


    WITH
    example AS
    (
    SELECT 1 id , 'Y'  attr FROM dual UNION ALL
    SELECT 2  , 'N'  FROM dual UNION ALL
    SELECT 2 id , 'Y'  FROM dual UNION ALL
    SELECT 1 , 'Y'  FROM dual
    )
    SELECT * FROM
       (
            SELECT
                c.*,
                ROW_NUMBER() OVER (partition BY id ,attr ORDER BY rownum ) rn
                           
            FROM example c
        );

     
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius