RE: SQL help: delete recs based on count

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: