Your question is a little ambigous. You talk of deleting duplicate rows but also say "I need to delete the oldest record based upon ACCT_ID".

Duplicate record deletion would get rid of 0, 1 or more rows for the same key -- keeping the "newest" load_date.

Deleting the oldest record by acct_id would always delete 1 record per unique acct_id.

Both are demonstrated here....


ops$tkyte@8i> select * from t;

ACCT_ID CUST_ID LOAD_DATE
---------- ------------------------- ----------
1234 ABCD 20000101
1234 DEFG 20000201
5678 STDF 20000201
5678 TRFD 20000101
9111 ADFG 20000201
9112 ASDX 20000201

6 rows selected.

there is our test data...


ops$tkyte@8i> delete from t
2 where load_date <> ( select max(load_date)
3 from t b
4 where b.acct_id = t.acct_id )
5 /

2 rows deleted.


This delete goes after ACCT_ID's that have more then 1 occurence and delete ALL but the "newest" -- the MAX. We get the max(load_date) for a given acct_id and unless you have that load_date -- you are deleted. This will delete 0, 1 or more rows per acct_id.

There should be an index on (acct_id,load_date)

ops$tkyte@8i> select * from t;

ACCT_ID CUST_ID LOAD_DATE
---------- ------------------------- ----------
1234 DEFG 20000201
5678 STDF 20000201
9111 ADFG 20000201
9112 ASDX 20000201

ops$tkyte@8i> rollback;
Rollback complete.

Now we can try again. This time, we pick the OLDEST record for an ACCT_ID and delete it -- regardless of the number of records with that ACCT_ID

ops$tkyte@8i> delete from t
2 where load_date = ( select min(load_date)
3 from t b
4 where b.acct_id = t.acct_id )
5 /

4 rows deleted.

ops$tkyte@8i>
ops$tkyte@8i> select * from t;

ACCT_ID CUST_ID LOAD_DATE
---------- ------------------------- ----------
1234 DEFG 20000201
5678 STDF 20000201