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 [EMAIL PROTECTED] (512)327-9068 -----Original Message----- Sent: Thursday, December 06, 2001 3:34 PM To: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
