Don

        Thanks for the suggestion, will just have to see what I can work
out.

Rodney

-----Original Message-----
From: Don Vawter [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 06, 2001 7:57 PM
To: CF-Talk
Subject: Re: SQL delete help


makes it a bit more complicated doesn't it.
You have no primary key in the table?
If you had one you might be able to use a correllated subquery.
In the absence of a key do define the items I don't see any way to do it
like I proposed.
You don't want to delete people but rather items and you show no key except
for the date and it is certainly
possible to have multiple rows with the same date.

I don't know if something like the following works at all. I haven't ever
tried to use a correllated subquery on a delete but you might want to try it
out on some test data. Even if it works when you only have one item per day
per person it can't possibly work for multiple items on a day.

 delete from tbl_person a
where date_entered  not in (
     select top 12 datentered from tbl_person b
        where b.person_id=a.person_id
       order by dateentered desc
)

----- Original Message -----
From: "Bruce, Rodney (Contractor)" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, December 06, 2001 3:46 PM
Subject: RE: SQL delete help


> 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
> >
> >
>
> 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Reply via email to