How do I delete specific older rows?
Posted on 2008-06-10
I have 2 tables.
my past rate table is really big and its slow when I query it so I want to clean it up ONCE a month by backing up useless/older rows from past rate to a 3rd table that I will call backuprates.
I created the backuprates by
I have carrier, country,rates, effective_dates as the important columns in all 3 tables
you can have multiple countries for the same carrier and multiple effective_dates for the same carrier
I want to have only the 2 most recent effective_dates per country in the past_rates table.
select carrier, country, rate, effective_date
group by carrier, country, rate, effective_date
order by carrier, country, effective_date desc
carrier country rate effective_date
asiatelecom france 0.51 2008-06-8
asiatelecom france 0.62 2008-05-12
asiatelecom france 0.72 2007-02-4
asiatelecom france 0.81 2005-03-24
asiatelecom egypt 1.5 2008-06-8
asiatelecom egypt 1.62 2008-01-22
.(lots of rows here)
zambiatele india 0.4 2008-05-4
zambiatele india 0.8 2007-06-2
zambiatele italy 0.6 2005-03-2
I want to keep the 2 most recent effective dates for all carriers and countries . .
If you could Please Help give a shot at my query, that will be fantastic!
If something is not clear , Please ask!