By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

delete oldest 20 rows from a table for each row

100+
P: 227
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
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
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

Post your reply

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