Delete last 35 days data from the table

Database
KVB
Enthusiast

Delete last 35 days data from the table

Hi

I had a table with n days of data.I need to delete last 35 days of data.My table doesnt have consecutive dates i.e. there were no loads in weekends or holidays..

Using INTERVAL command may just calculate the difference.So it wont work out.

I can use RANK to find the sequence.Is there any other better way to write a delete statement.

2 REPLIES
Enthusiast

Re: Delete last 35 days data from the table

This should work for you,

    DEL FROM db.tb WHERE t_Date IN

    (SEL t_date FROM

    ( SEL   t_date, RANK(t_date)  RD FROM (SEL t_date FROM db.tb GROUP BY 1)   D ) D2

    WHERE RD <= 25)

Robert

Enthusiast

Re: Delete last 35 days data from the table

Hi,

How about ...... qualify count(transdate) over (partition by transdate rows between unbounded preceding  and unbounded following) = 35 in your query limit.

Thanks and regards,

Raja