Re: Help with duplicate records in SQL

In article <1992Jun4.212719.3024_at_ncube.com> sbh_at_ncube.com (Shawn Honess) writes:
>I have a table with some duplicate records. I would like
>to delete all duplications, leaving one of each record. I
>use essenitally the following search to find the duplicates:
 

> SELECT part, name, vendor, status
> FROM bigtable
> GROUP BY part, name, vendor, status
> HAVING COUNT(*) > 1 <=== this is the key
> ORDER BY part, name, vendor, status
> ;

Try

	DELETE FROM bigtable
	WHERE	rowid =  (
		SELECT	max(rowid)
		FROM	bigtable
		GROUP BY part, name, vendor, status
		HAVING	COUNT(*) > 1
	)
	;

--
___ __ ___________________	Alvin W. Law      alaw_at_us.oracle.com
   /  | __ __ __ __ __ __	Manufacturing Applications          () `o
  / * |/ / \ ' // //  ' /	Oracle Corporation                 (  )( )  
 /__\_|_/_/ \_//_//_/\_/	Phone : 415/506-3390             __-mm--m---
__________________________	Fax   :	415/506-7299          ---^\

Segmentation fault (core dumped) Received on Mon Jun 08 1992 - 23:34:51 CEST