Hello, i am definitely sure that the statement below would mess up your data. The rowid is an internal (physical) access path to your data and it is fatal to use it as a logical sorting criteria.
To delete ALL duplicates: see my suggestion below. To leave the first element in your table, you will at first have to define, which one actually IS the first duplicate in your table. For example you may only consider your key field 'id': REM get/check the 'first' of your duplicates (key-rowid-pairs): select id, min(rowid), count(*) from table group by id having count(*) > 1; REM delete duplikates, ignoring the 'first' REM rememer: i did not actually check this code; but i think it is OK; youn may let me know... delete from table where id in ( select id from table group by id having count(*) > 1 ) and (id, rowid) <> ( select id, min(rowid) from table group by id having count(*) > 1 ); On the other hand you my define the 'first' as a pair of two (or more) fields. I did not check this... But in every case you will loose the information stored in the extra fields of your table. I would rather suggest to seriously review your data model ... cu, Christian ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "Tim Bunce" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, September 22, 2003 6:36 PM Subject: Re: SQL statement to find and delete double entries > Thanks, Tim. Adding Oracle to your search yielded the following quickly. > > delete from T t1 > where t1.rowid > > ( select min(t2.rowID) from T t2 > where t1.col1 = t2.col1 > and t1.col2 = t2.col2); > > I ought to know better and just go googly early. > > > > ____________________________ > Jeff Seger > Fairchild Semiconductor > [EMAIL PROTECTED] > ____________________________ > > > > > > Tim Bunce <[EMAIL PROTECTED]> > 09/19/2003 03:09 PM > > > To: Jeffrey Seger/Corporate/[EMAIL PROTECTED] > cc: Christian Merz <[EMAIL PROTECTED]>, [EMAIL PROTECTED], > "Morrison, Trevor (Trevor)" <[EMAIL PROTECTED]> > Subject: Re: SQL statement to find and delete double entries > > > > It's a common problem. You can start here: > > http://www.google.com/search?as_q=sql+delete+duplicate > > and add the name of the database your using. > > Tim. > > On Fri, Sep 19, 2003 at 01:31:20PM -0400, [EMAIL PROTECTED] > wrote: > > The only problem with that approach is that it deletes all of the > entries > > and doesn't leave "singles" behind. I'd probably do it programatically. > > > Grab the results of query 1, store the data in a hash of hashes, then do > > > the delete and re-insert. > > > > But I'd love to hear an SQL solution to leaving one copy of each > duplicate > > behind. > > > > > > > > ____________________________ > > Jeff Seger > > Fairchild Semiconductor > > [EMAIL PROTECTED] > > ____________________________ > > > > > > > > > > > > "Christian Merz" <[EMAIL PROTECTED]> > > 09/18/2003 08:33 AM > > > > > > To: "Morrison, Trevor (Trevor)" <[EMAIL PROTECTED]>, > <[EMAIL PROTECTED]> > > cc: > > Subject: Re: SQL statement to find and delete double > entries > > > > > > Hi, > > > > the basic idea to find duplicate or multiple values is: > > select id, count(*) > > from table > > group by id > > having count(*) > 1; > > > > to delete ALL such values you may do this: > > delete from table where id in > > ( select id > > from table > > group by id > > having count(*) > 1 > > ); > > > > cu, Christian > > > > ----- Original Message ----- > > From: "Morrison, Trevor (Trevor)" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Saturday, August 16, 2003 6:39 PM > > Subject: SQL statement to find and delete double entries > > > > > > Hi, > > > > What would be an SQL statement that will find duplicate order numbers in > > table and then delete them? > > > > TIA > > > > Trevor > > > > > > > > > > > > > >
