RE: SQL to find duplicate rows...

From: HAWKINS, JAMES W [IT/1000] <james.w.hawkins_at_pharmacia.com>
Date: Fri, 07 Dec 2001 07:33:35 -0800
Message-ID: <F001.003D76D2.20011207065518@fatcity.com>

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

Received on Fri Dec 07 2001 - 09:33:35 CST