Query to get 10 oldest rows of a table

From: Eric <bergeroner_at_rmoc.on.ca>
Date: Mon, 21 Jun 1999 17:32:04 GMT
Message-ID: <01bebc0a$b2c98a40$a60c6480@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 Mon Jun 21 1999 - 12:32:04 CDT