I think I see were you are going. But I didn't give enough information I think.
the table looks something like this: userid item1 item2 dateentered 11 1 2 1/1/00 11 3 4 1/2/00 11 6 7 1/3/00 12 3 5 1/5/00 <--and so forth---> So if user 11 has 15 records I want to delete 3 of them (oldest) but if I understand your query correctly, it would delete all but 12 users from the table?? I might have 50 user with any number of records each. Does this help or just thicken the mud :o? Thanks, Rodney -----Original Message----- From: Don Vawter [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 06, 2001 3:34 PM To: CF-Talk Subject: Re: SQL delete help delete from tbl_person where person_id not in ( select top 12 person_id from tbl_person order by dateentered desc ) or something like that. The subquery chooses the 12 most recent and then you just the rest. ----- Original Message ----- From: "Bruce, Rodney (Contractor)" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, December 06, 2001 3:24 PM Subject: OT: SQL delete help > Is there a way to delete a number of records in one SQL statement? > i.e. I want to maintain a history of 12 records per user. Once a month > when I run my scheduled tasks I would like to delete the oldest records over > the 12. > > Deleting the oldest record isn't a problem. but if I need to delete 4 > records how would I do this with out looping over the query 4 times? > > using CF5/NT4.0/SQL7.0 > > Thanks > Rodney > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
