Re: Help with duplicate records in SQL

In article <50845_at_seismo.CSS.GOV> cooper_at_beno.CSS.GOV (Dale Cooper) writes:
>In article <ALAW.92Jun8133451_at_ap1seq.oracle.com> alaw_at_us.oracle.com writes:
>>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:
>
>-> [incorrect answer #1]
>
>-Try
>-
>- [incorrect answer #2]
>
>BZZZZZT!
>
>instead, try: [incorrect answer #3]
>
> DELETE FROM bigtable
> WHERE rowid in (
> SELECT max(rowid)
> FROM bigtable
> GROUP BY part, name, vendor, status
> HAVING COUNT(*) > 1);
But what if there are three (or more)-way duplicates? (This select is also simpler.)

     DELETE FROM bigtable
     WHERE   rowid > (
	     SELECT  min(rowid)
	     FROM    bigtable
	     GROUP BY part, name, vendor, status);

-- 
"Computers are useless.  They can only give you answers." - Pablo Picasso
Kenneth D Huffman   Work: Chemical Abstracts Service    Play: 2740 Wynnerock Ct
<kxh26_at_cas.org>           Box 3012, Columbus OH 43210         Hilliard OH 43026
<kxh26_at_cas.bitnet>        614-447-3838 ext. 2290              614-771-6334