467,087 Members | 1,242 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,087 developers. It's quick & easy.

delete oldest 20 rows from a table for each row

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
  • viewed: 1258
1 Reply
Expert 2GB
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 ?

Nov 29 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by jeff | last post: by
12 posts views Thread by hallpa1@yahoo.com | last post: by
2 posts views Thread by =?Utf-8?B?TU1TSkVE?= | last post: by
3 posts views Thread by =?Utf-8?B?S2F5xLFoYW4=?= | last post: by
2 posts views Thread by Michael | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.