Bigbears
asked on
How do I delete specific older rows?
Hi,
I have 2 tables.
1. current_rate
2. past_rates
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
select *
into backuprates
from past_rate
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.
example:
select carrier, country, rate, effective_date
from past_rate
group by carrier, country, rate, effective_date
order by carrier, country, effective_date desc
gives me:
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!
THANK YOU.
I have 2 tables.
1. current_rate
2. past_rates
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
select *
into backuprates
from past_rate
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.
example:
select carrier, country, rate, effective_date
from past_rate
group by carrier, country, rate, effective_date
order by carrier, country, effective_date desc
gives me:
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!
THANK YOU.
ASKER
Hi Angellll,
Thanks for looking into this. The code you provided is almost right. the SELECT TOP 2 i.effective_date ..
selects the highest ones for that subquery instead of the the most recent ones.
If we were allowed to put 'order by effective_date desc' in that subquery, it would work..
unless im missing something?
Thanks for looking into this. The code you provided is almost right. the SELECT TOP 2 i.effective_date ..
selects the highest ones for that subquery instead of the the most recent ones.
If we were allowed to put 'order by effective_date desc' in that subquery, it would work..
unless im missing something?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again!
A small correction to what Angellll has specified:
DELETE t
FROM past_rate t
WHERE t.effective_date NOT IN ( SELECT TOP 2 i.effective_date FROM past_rate i WHERE i.country = t.country and i.carrier = t.carrier order by i.effective_date desc)
Order by in inner query is required since author requires latest 2 records by time.
DELETE t
FROM past_rate t
WHERE t.effective_date NOT IN ( SELECT TOP 2 i.effective_date FROM past_rate i WHERE i.country = t.country and i.carrier = t.carrier order by i.effective_date desc)
Order by in inner query is required since author requires latest 2 records by time.
Open in new window