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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Barbara Baker   Sent: Thursday, December 29, 2005 5:42 PM   To: oracle-l_at_freelists.org
  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 Received on Fri Dec 30 2005 - 01:07:30 CST