Re: Query to get 10 oldest rows of a table

we had this same type of problem at work so we had to change the design of the database a little. I don't know if this is possible in your case but I will explain.

1. Added a new table called LastClients ID number // relationship and primary key dataAdded // when the client was added
2. put a BeforeInsert trigger on the Client table. The trigger inserts the ClientID and DateAdded to the LastClients tables. Then do a query on the LastClients table order by dateAdded. The first record is the record that should be delete since it is not in the top ten.

Now you can always find top ten clients.

Another way of handling this problem is using a a After Insert trigger. You need to add a column to the Client table called EffectiveOrder. In the BeforeInsert, do an update command to add 1 to the EffectiveOrder. Also the client record that you are trying to insert must have the EffectiveOrder set to 1. Now all your records has an order. The only problem with this is if the Client table has a lot of records. Then the update will kill the preformance of this approach.

John

Eric <bergeroner_at_rmoc.on.ca> wrote in message news:01bebc0a$b2c98a40$a60c6480_at_w15-1429.rmoc.on.ca...
> Suppose the following client table defined as:
> first_name varchar(30)
> last_name varchar(30)
> effective_date date
> with an index on effective_date.
>
> The application would like the oldest 10 clients so I wrote the following
> query:
>
> select /*+FIRST_ROWS+*/ a.first_name , a.last_name, a.effective_date
> from client a
> where 10 >= (select /*+FIRST_ROWS+*/ count(effective_date)
> from client b
> where b.effective_date <= a.effective_date)
> order by a.effective_date asc
> ;
>
> which works fine. The problem is that the query takes several minutes to
> execute... Does anyone know a better way of writing this type of query?
>
Received on Sun Jun 27 1999 - 16:37:34 CDT