Delete older than N days distinct entries

Question

I have to delete data for each Account + date_added combination which is more than N days old. By N days old, it means here deleting older than first N distinct occurrences for above combination

DELETE
FROM tbl_check 
   WHERE
   N  <  (SELECT count(DISTINCT date_added) + 1
   FROM tbl_check b
   WHERE b.ACCOUNT_NUMBER = tbl_check.ACCOUNT_NUMBER
     AND tbl_check.date_added < b.date_added)

This query must run on both Sybase ASE and Oracle. I have written so far above query which is trying to simulate DENSE_RANK and is successfully deleting based on ranking.

However it does not use any index and is also a correlated query doing a FTS. There could be thousands of rows in this table. enter image description here

There is a composite PK which is : FOREIGN_KEY_COL , ACCOUNT_NUMBER, date_added , .. Is this query up to the job or we can improve upon it given same query has to work on both databases as mentioned.

Also some way to make this DELETE buffered would be nice – fortm Sep 11 '14 at 14:25 — fortm, Sep 11 '14 at 14:25

score 0 · Answer 1 · answered Sep 11 '14 at 14:56

0

why you dont use below query ;

DELETE FROM tbl_check  where (getdate()-date_added)> N

answered Sep 11 '14 at 14:56

Sefer K

504
1
5
19

this is because date_added is not same for each account_number and so for Account1 , data_added could be SYSDATE , SYSDATE -1 , SYSDATE -2 . Account2 can have only SYSDATE -2, SYSDATE -4, SYSDATE -5. If N = 2 , then after cleanup, Account1 should delete SYSDATE -2 and account2 should delete SYSDATE -5 – fortm Sep 11 '14 at 18:53

Delete older than N days distinct entries

1 Answers1