Re: PL/SQL script to clean a table

[Quoted] hilmarcher_at_yahoo.com (Caroline) wrote in news:8f934c98.0204240231.5457e004_at_posting.google.com:

> HI all,
> I have a table with so many entries every day so I want to clean it.
>
> table name : service
> table attributes : code, location, date
>
> the goal is to delete each records where :
>
> 1) I can find the same code AND location
> 2) date > one month ago
>
> BUT : I have to keep the last record with this code and location
> before one month.
>
> example:
> 1)code=1 location=a date=20/04/02
> 2)code=1 location=a date=20/03/02
> 3)code=1 location=a date=21/03/02
>
> *I keep the record 1) beacuse the date < one month
> *I keep the 3) and I delete the 2) record because code and location
> are the same, date > one month
> and 3.date > 1.date
>
> And I don't know how to do that ...
>
> Many thanks
>
> Caroline

delete from service a where a.date < (select max(b.date) from service b where b.code = a.code and b.location = a.location and b.date < add_months(sysdate,-1));

There is one problem with this though and that is if there are two rows with the exact same date and that is the most recent service record over a month old for this code and location. If that happens both will be kept instead of just one.

-- 
Ken Denny
http://www.kendenny.com/

"What does an actor know about politics?" - Ronald Reagan, complaining 
about Screen Actors' Guild president Ed Asner speaking out on US foreign 
policy