deleting duplicate rows

From: Ron McCamish <rwm26_at_cas.org>
Date: 5 Aug 92 22:21:54 GMT
Message-ID: <1992Aug5.222154.8447_at_cas.org>

  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:

  select * from wfi_master;

  REG_NO CASSET ENTRY_DAT

• ------ --------- 000000001 000000 11-JUN-92 000000001 000001 11-JUL-92 000000002 000001 11-JUN-92 000000003 000005 11-JUN-92 000000004 000003 11-JUN-92 000000004 000004 11-JUL-92 000000005 000017 11-JUN-92

  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:

  select * from wfi_master;

  REG_NO CASSET ENTRY_DAT

• ------ --------- 000000001 000001 11-JUL-92 000000002 000001 11-JUN-92 000000003 000005 11-JUN-92 000000004 000004 11-JUL-92 000000005 000017 11-JUN-92

  Thus, the reg_no is unique and the rows with the same reg_no and lesser   cassette_no has been deleted.

  I messed around with group by, having and several correlated sub-queries  but I still can't find the right combo.

  Can anyone point me in the right direction? btw, the full blown table  has 146K rows with a unique index on the reg_no and cassette_no fields  together. And I'm somewhat of a sql*plus novice....

  thanks,

  ron....

---
Ron McCamish
BITNET: rwm26_at_cas.bitnet
UUCP:   ...!osu-cis!chemabs!rwm26
INET:   rwm26_at_cas.org

Received on Thu Aug 06 1992 - 00:21:54 CEST