community.borland.com

Article #25320: What is the SQL-equivalent of GDML's FIRST clause

Problem:
If a user is coming from a GDML background or they are converting
 a trigger/stored procedure from GDML to SQL, the user will find that
 the FIRST clause is not supported in SQL.

Solution:
The information in this article applies to:

* InterBase 4.x
* InterBase 5.x

There is no single SQL command that replaces the FIRST clause,
 but it is possible through a series of nested selects and the max()
 operator as long as the where clause references the key column. 
 Here is an example using a standard customers table:

select company from customers
where cust_no >= (select max(cust_no) from customers and
where cust_no < (select max(cust_no) from customers))

In this example, cust_no is the key field.  If you wanted to do the same
 for a non-key column (e.g.: company), it wouldn't work:

select company from customer
where company >= (select max(company) from customer
where company < (select max(company) from customer))

because InterBase is ultimately comparing values, not record locations.
 Furthermore, the order by clause doesn't seem to work either.

Note that you can get the first x records only if the key is ascending or the
 last y records if the key is descending (using the min() operator).

Last Modified: 17-OCT-00