Re: deleting duplicate rows

In article <1992Aug5.222154.8447_at_cas.org> rwm26_at_cas.org writes:
>
> I have the following table:
>
> desc wfi_master
>
> Name Null? Type
> ------------------------------- -------- ----
> REG_NO NOT NULL CHAR(9)
> CASSETTE_NO NOT NULL CHAR(6)
> ENTRY_DATE NOT NULL DATE
>
> which has duplicate REG_NO fields. Thus, a dump of the table might look
> like this:
>
> reg_no = 1 and 4 are duplicates. I would like to delete the duplicate
> row on the table where the cassette_no is the lesser of the
> duplicate reg_no. So after I ran this amazing sql*plus delete statement to
> perform this duplicate weeding, the tables should look like this:
>

  Since we've been having problems and we have a lot of duplicate data

  I've suggested the following script

   Delete from wfi_master
   where rowid in (select rowid from wfi_master

	   minus
		   select min(rowid) from wfi_master
		   group reg_no, cassette_no);

 The support teams (in charge of porting the data from one database  to another are now using varations of these script to ensure that the  data meets our requirments.

-- 
         Kermit Tensmeyer                         | Intergraph Corporation
Life is sometimes a bowl of Cherries; Watch out for the pits;
   klt_at_kt8127.b23a.ingr.com                       |