Howdy, Stranger!

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

Categories

"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.