Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

"how to find duplicate records in oracle data base"

I would like to know the oracle (SQL) query to find the duplicate records in a oracle data base.
Kindly help me.
Thanks in advance,
Bye

Comments

  • GothmordrinGothmordrin Posts: 10Member
    : I would like to know the oracle (SQL) query to find the duplicate records in a oracle data base.
    : Kindly help me.
    : Thanks in advance,
    : Bye
    :

    By duplicate records I presume you mean records with similar keys?
    (Which begs the question as to why your primary key constraint didn't kick out the duplicate entries!)

    Try this:-

    select *
    from table
    group by key1, key2, ..., keyN
    having count (*) > 1;

    If you want to list them for a data cleanup excercise add an order by clause to list them in the order you want.

    Gothmordrin
    Think it through first - then pester someone.

  • sabeerpashasabeerpasha Posts: 1Member
    U can find and delete the Duplicate files

    delete from test a
    where rowid <> ( select max(rowid)
    from test b
    where a.sno = b.sno
    and a.sname = b.sname )

    This querry will find duplicate rows and delete those.

    Regards,
    Pasha
  • benf101benf101 Posts: 1Member
    This way worked for me:

    SELECT COL_TO_CHECK, COUNT(COL_TO_CHECK)
    FROM TABLE_NAME
    GROUP BY COL_TO_CHECK
    HAVING COUNT(COL_TO_CHECK) > 1


  • beerandmilkbeerandmilk Posts: 5Member
    This post has been deleted.
Sign In or Register to comment.