[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

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.
0
Bigbears
Asked:
Bigbears
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
something like this:
--- first the select to see what will be deleted:
SELECT 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 ) 
--- if the select seems fine, run the delete: 
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 )

Open in new window

0
 
BigbearsAuthor Commented:
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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
indeed:

--- first the select to see what will be deleted:
SELECT 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 ) 
--- if the select seems fine, run the delete: 
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  )

Open in new window

0
 
BigbearsAuthor Commented:
Thanks again!
0
 
roshkmCommented:
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.

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now