Re: SQL help: delete recs based on count

Hi Barbara,

Based on my understanding, I have written a piece of code that might help. Pls test and use it if it meets your requirement.

Best Regards

Sriram Kumar

===========Code========================

declare

  /* Fetch Personal Id's Having more than 100 Records */    cursor cur_delete is
   select a.personal_id
   from LAST_ACCESSED_LIST a
   group by a.personal_id
   having count(*) >=100 ;

   lr_rowid rowid;

  begin

  for j in cur_delete
   loop

       /* Select rowid of the 99 record. Any record


       *   that has rowid greater than this is eligible

       *   for deletion */


       select max(rowid_99)
       into lr_rowid
       from
       ( select rowid rowid_99
          from LAST_ACCESSED_LIST b
          where b.personal_id=j.personal_id
          and rownum 99th
record
        *  Need Be rewrite this as bulk delete with limit 5000
        */
       delete from LAST_ACCESSED_LIST c
       where c.personal_id=j.personal_id  and c.rowid > lr_rowid;

       dbms_output.put_line('Deleted ' || sql%rowcount);

   end loop;

  end ;

=============end of code=========================

On 12/29/05, Barbara Baker wrote:

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