FAQ
Hello all,

I know this has come across many times now, and I thought I had it saved
somewhere - you know how that goes...

I need to query a table to find all duplicate rows based on a given column.
Don't feel like re-inventing the wheel...

Thanks in advance,
Jim

Jim Hawkins
Oracle Database Administrator
Data Management Center of Expertise

Pharmacia Corporation
800 North Lindbergh Blvd.
St. Louis, Missouri 63167

Work (314) 694-4417
Cellular (314) 724-9664
Pager (314) 294-9797

james.w.hawkins_at_pharmacia.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: HAWKINS, JAMES W [IT/1000]
INET: james.w.hawkins_at_pharmacia.com

Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Jamadagni, Rajendra at Dec 6, 2001 at 9:24 pm
    select column1, count(*)
    from my_table
    group by column1
    having count(*) > 1

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    *********************************************************************2

    This e-mail message is confidential, intended only for the named recipient(s) above
    and may contain information that is privileged, attorney work product or exempt from
    disclosure under applicable law. If you have received this message in error, or are
    not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
    and delete this e-mail message from your computer, Thank you.

    *********************************************************************2
  • Jack C. Applewhite at Dec 6, 2001 at 10:09 pm
    Jim,

    Oops! Right after I sent this, I saw that you asked to find, not delete,
    duplicates.

    Sorry, I've spent the last two days de-duping a bunch of tables, so I've got
    deletion on the brain.

    How about:

    Select SomeColumn
    From MyTable
    Where MyColumn In
    (
    Select MyColumn
    From My Table
    Group By MyColumn
    Having Count(*) > 1
    );

    Jack

    Jack C. Applewhite
    Database Administrator/Developer
    OCP Oracle8 DBA
    iNetProfit, Inc.
    Austin, Texas
    www.iNetProfit.com
    japplewhite_at_inetprofit.com
    (512)327-9068

    -----Original Message-----
    Sent: Thursday, December 06, 2001 3:34 PM
    To: ORACLE-L_at_fatcity.com

    Jim,

    An option that handles multiple duplicates is:

    Delete
    From MyTable
    Where (MyColumn,RowID) In
    (
    Select MyColumn,RowID
    From MyTable
    Minus
    Select MyColumn,Min(RowID)
    From MyTable
    Group By MyColumn
    );

    You could use Max(RowID) as well, depending on your needs.

    Jack

    Jack C. Applewhite
    Database Administrator/Developer
    OCP Oracle8 DBA
    iNetProfit, Inc.
    Austin, Texas
    www.iNetProfit.com
    japplewhite_at_inetprofit.com
    (512)327-9068

    -----Original Message-----
    JAMES W [IT/1000]

    Sent: Thursday, December 06, 2001 2:26 PM
    To: Multiple recipients of list ORACLE-L

    Hello all,

    I know this has come across many times now, and I thought I had it saved
    somewhere - you know how that goes...

    I need to query a table to find all duplicate rows based on a given column.
    Don't feel like re-inventing the wheel...

    Thanks in advance,
    Jim

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack C. Applewhite
    INET: japplewhite_at_inetprofit.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jack C. Applewhite at Dec 6, 2001 at 10:30 pm
    Jim,

    An option that handles multiple duplicates is:

    Delete
    From MyTable
    Where (MyColumn,RowID) In
    (
    Select MyColumn,RowID
    From MyTable
    Minus
    Select MyColumn,Min(RowID)
    From MyTable
    Group By MyColumn
    );

    You could use Max(RowID) as well, depending on your needs.

    Jack

    Jack C. Applewhite
    Database Administrator/Developer
    OCP Oracle8 DBA
    iNetProfit, Inc.
    Austin, Texas
    www.iNetProfit.com
    japplewhite_at_inetprofit.com
    (512)327-9068

    -----Original Message-----
    JAMES W [IT/1000]

    Sent: Thursday, December 06, 2001 2:26 PM
    To: Multiple recipients of list ORACLE-L

    Hello all,

    I know this has come across many times now, and I thought I had it saved
    somewhere - you know how that goes...

    I need to query a table to find all duplicate rows based on a given column.
    Don't feel like re-inventing the wheel...

    Thanks in advance,
    Jim

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack C. Applewhite
    INET: japplewhite_at_inetprofit.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • HAWKINS, JAMES W [IT/1000] at Dec 7, 2001 at 3:33 pm
    Thank you everyone! We found the culprits....

    Jim

    Jim Hawkins
    Oracle Database Administrator
    Data Management Center of Expertise

    Pharmacia Corporation
    800 North Lindbergh Blvd.
    St. Louis, Missouri 63167

    Work (314) 694-4417
    Cellular (314) 724-9664
    Pager (314) 294-9797

    james.w.hawkins_at_pharmacia.com

    -----Original Message-----
    Sent: Thursday, December 06, 2001 3:35 PM
    To: Multiple recipients of list ORACLE-L

    Jim,

    Oops! Right after I sent this, I saw that you asked to find, not delete,
    duplicates.

    Sorry, I've spent the last two days de-duping a bunch of tables, so I've got
    deletion on the brain.

    How about:

    Select SomeColumn
    From MyTable
    Where MyColumn In
    (
    Select MyColumn
    From My Table
    Group By MyColumn
    Having Count(*) > 1
    );

    Jack

    Jack C. Applewhite
    Database Administrator/Developer
    OCP Oracle8 DBA
    iNetProfit, Inc.
    Austin, Texas
    www.iNetProfit.com
    japplewhite_at_inetprofit.com
    (512)327-9068

    -----Original Message-----
    Sent: Thursday, December 06, 2001 3:34 PM
    To: ORACLE-L_at_fatcity.com

    Jim,

    An option that handles multiple duplicates is:

    Delete
    From MyTable
    Where (MyColumn,RowID) In
    (
    Select MyColumn,RowID
    From MyTable
    Minus
    Select MyColumn,Min(RowID)
    From MyTable
    Group By MyColumn
    );

    You could use Max(RowID) as well, depending on your needs.

    Jack

    Jack C. Applewhite
    Database Administrator/Developer
    OCP Oracle8 DBA
    iNetProfit, Inc.
    Austin, Texas
    www.iNetProfit.com
    japplewhite_at_inetprofit.com
    (512)327-9068

    -----Original Message-----
    JAMES W [IT/1000]

    Sent: Thursday, December 06, 2001 2:26 PM
    To: Multiple recipients of list ORACLE-L

    Hello all,

    I know this has come across many times now, and I thought I had it saved
    somewhere - you know how that goes...

    I need to query a table to find all duplicate rows based on a given column.
    Don't feel like re-inventing the wheel...

    Thanks in advance,
    Jim

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack C. Applewhite
    INET: japplewhite_at_inetprofit.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: HAWKINS, JAMES W [IT/1000]
    INET: james.w.hawkins_at_pharmacia.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 6, '01 at 9:22p
activeDec 7, '01 at 3:33p
posts5
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase