Delete Query (date, amount records)

[Grudge]

Hey There,

First off, thanks for all the help I've gotten so far. It's really helped me.

Aha, but I have some more questions :

I have a table full of records, and I want to create queries that do the following :

1) Delete all the records except the last x amount of records (ordered by date)

and

2) Delete all the records except the records from the last x days (ordered by date)

Any help on this would be greatly appreciated...

Tom Van den Bon

 

[essa2000]

Dear you can delete all the record execpt X Amount of record in this way if you know that the X amount in percent or in count

Delete from <TableName>
Where <PrimaryID of this table> not in
(Select top 10 percent <PrimaryID of this table> from <TableName> Order By <PrimaryID of this table> Desc)

OR

Delete from <TableName>
Where <PrimaryID of this table> not in
(Select top 1000 <PrimaryID of this table> from <TableName> Order By <PrimaryID of this table> Desc)

You can also use the date in order by clause


you can delete all the records except X last days of record in the same after using dateadd function to get the records of last -X days and then use the NOT IN query to delete records.




Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com

 

[essa2000]

Dear for you;


Declare @L_Days int

SET = 10 -- X last days

Delete from <TableName>
Where <PrimaryID of this table> NOT IN (Select <PrimaryID of this table> from <TableName>
Where Date > DateAdd(day , - @L_Days , getdate())
)

It will delete all the records except records of last 10 days
Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com