Re: SQL help: delete recs based on count
- From: Barbara Baker <barb.baker@xxxxxxxxx>
- To: Glenn Santa Cruz <glenn.santacruz@xxxxxxxxx>
- Date: Fri, 30 Dec 2005 09:30:58 -0700
You are all AWESOME!! Thanks Mark, Sriram, and Glenn for your suggestions.
The developer and I are testing now to see which solution works best.
Thanks for taking the time to help out!!
Barb
On 12/30/05, Glenn Santa Cruz <glenn.santacruz@xxxxxxxxx> wrote:
>
> And if you don't care about the company or date (just care about the
> personnel_id having more than 100 rows), you could use analytics:
>
> delete from last_access_list
> where personnel_id in (
> select personnel_id
> from (select personnel_id,
> count(*) over( partition by personnel_id ) cnt
> from last_access_list
> )
> where cnt > 100
> )
> /
>
>
> On 12/29/05, Mark W. Farnham <mwf@xxxxxxxx> wrote:
> >
> >
> > You mean like
> >
> > delete from last_accessed_list
> > where (personnel_id,company_id) in
> > (select personnel_id,company_id from (select
> > personnel_id,company_id,count(*) from last_accessed_list
> > group by personnel_id,company_id having count(*) > 100));
> >
> > or possibly you don't care about whether the personnel_id is in total
> over
> > 100 across all companies (although that might be a different person from
> the
> > available information),
> >
> > then
> >
> > delete form last_accessed_list
> > where personnel_id in
> > (select personnel_id from (select personnel_id,count(*) from
> > last_accessed_list group by personnel_id having count(*) > 100));
> >
> > Now, whether that is the fast way to do this is an open question. If the
> > number of rows to be deleted is somewhere over 1/3 of the total rows,
> then
> > you're probably better off
> >
> > create keep_last_accessed_list.....
> >
> > insert into keep_last_accessed_list
> > select personnel_id,company_id,access_date from
> > last_accessed_list
> > where personnel_id in (select personnel_id from (select
> > personnel_id,count(*) from last_accessed_list group by personnnel_id
> having
> > count(*) < 101));
> >
> > rename last_accessed_list obsolete_last_accessed_list;
> > rename keep_last_accessed_list last_accessed_list;
> > create your indexes.
> >
> > You get the idea - probably typos in there, I'm just typing this in, not
> > testing it.
> >
> >
> > -----Original Message-----
> > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx]On
> > Behalf Of Barbara Baker
> > Sent: Thursday, December 29, 2005 5:42 PM
> > To: oracle-l@xxxxxxxxxxxxx
> > Subject: SQL help: delete recs based on count
> >
> >
> >
> > OpenVMS 7.3-1; Oracle 9.2.0.4
> >
> > The table LAST_ACCESSED_LIST has 3 columns
> >
> > Name Null? Type
> > ----------------------------------------- --------
> > ---------
> > PERSONNEL_ID NOT NULL NUMBER(9)
> > COMPANY_ID NOT NULL NUMBER(9)
> > ACCESS_DATE DATE
> >
> > I want to (actually, developer wants to) remove all the records from the
> > table for a given user (a specific personnel_id)
> > where user has more than 100 records. A user currently may have as few
> as 1
> > record or as many as 1000.
> >
> > I cannot come up with syntax. This is definitely NOT what I want
> >
> > select personnel_id, count(*) from last_accessed_list
> > group by personnel_id having count(*) < 20
> >
> > but I cannot figure out how to count/gather the total number of records
> for
> > a specific personnel_id.
> > There is a primary key on personel_id + company_id, if this helps.
> >
> >
> > Any help greatly appreciated. Thanks!
> >
> > Barb
>
Other related posts:
