delete oldest 20 rows from a table for each row

samvb

I have a table that stores details of a member. Its structure is:

memberid punchdatetime

Each member can have many, many punch dates and times. Since the db can be full and search can be slowed down, I want to clean/delete punch information of each member. If a member have less than or equal to 20 rows in the table then no information of him is going to be deleted from the table. If however it exceeds 20, the oldest rows must be deleted. How can I just do that?

Nov 29 '11 #1

nico5038

With a DELETE query using a WHERE clause with the (NOT) EXISTS option referring to a (SELECT TOP 20 FROM tblYours b WHERE b.PersonsID = a.PersonsID).

Getting the idea ?

Nic;o)

Nov 29 '11 #2