All Forums Database
KVB 124 posts Joined 09/12
07 Oct 2013
Delete last 35 days data from the table

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.

Glass 225 posts Joined 04/10
07 Oct 2013

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)

Raja_KT 1246 posts Joined 07/09
07 Oct 2013




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 K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.