Solved

How do I delete specific older rows?

Posted on 2008-06-10
5
244 Views
Last Modified: 2010-04-21
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
Comment
Question by:Bigbears
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21756761
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
 

Author Comment

by:Bigbears
ID: 21756990
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 21757026
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
 

Author Closing Comment

by:Bigbears
ID: 31465979
Thanks again!
0
 
LVL 4

Expert Comment

by:roshkm
ID: 21757929
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question