Purge all but last 100 recs

  • Thread starter Thread starter UpRider
  • Start date Start date
U

UpRider

How would I purge all records in a table except for the last 100 recs added?
TIA, UpRider
 
---------- "UpRider said:
How would I purge all records in a table except for the last 100 recs added?
TIA, UpRider
Click to expand...

What do you mean with "100 last recs"? The newest 100 when sorted by
creation date+time? Is there a field containing the date +time of
creation? Or do you have an autonumber primary key?

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Yes, there is an autonumber. I also notice that when I view the table
contents, records are always added at the end of the table, thus the last
100 are the last 100 added.
In addition, one of the fields is a date/timestamp. I would define the last
100 as the most recent timestamps.
UpRider
 
You can use sql statement

DELETE * FROM <table_name> WHERE <AUTONUMBER_Col> NOT IN (SELECT TOP 100(<AUTONUMBER_Col>) FROM <table_name> ORDER BY <AUTONUMBER_Col> DESC)

replace <table_name> with the name of the table with your records and
<AUTONUMBER_Col> with the field name of the autonumber field.

Hope this helps.

regards,
yann
 
Back
Top