SQL help: delete recs based on count

From: Barbara Baker <barb.baker_at_gmail.com>
Date: 2005-12-29 23:41:58
Message-id: 47a6f72b0512291441u6a5867c4m2753917074b08bd2@mail.gmail.com

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 Thu Dec 29 2005 - 23:41:58 CST