--- In firebird-support@yahoogroups.com, "russellbelding" <russell@...>
wrote:

>
> In the database is
> ATable with fields FIELD_PK, FIELD2, .... and FIELD_PK is the primary
> key field.

No, see Alan's answer.

>
> In a stored procedure I use
> select FIRST 1 FIELD_PK from ATABLE
> where FIELD2 > 0

In the above query, you ask the database engine to return the first
FIELD_PK it just happens to find that matches your where criteria. That
may or may not be the lowest.

You would use such syntax if you did not care which value was returned,
providing for that record FIELD2 > 0. You are saying to the database
engine that you have no concern as to the ordering of the results.

> select FIRST 1 FIELD_PK from ATABLE
> where FIELD2 > 0
> order by FIELD_PK?
>

In the above query, you are now telling the database engine that not
only do you expect Field 2 > 0, you also want the records returned in a
set order. In the first case, you want the results ASAP - dont bother
with sorting because you don't care. In this case, you want the results
sorted - dont bother me until this has been done.

It is by the way logically equivalent to the following query:

select min(FIELD_PK)
from ATABLE
where FIELD2 > 0

(I do not know whether one is internally converted to the other or not)

You need to pretend that the results are returned in an undefined order
unless you provide an order by clause. Similarly, if you provide an
order by clause, but multiple records are identical in all of the
fields in it, then those subset of records will be returned in an
undefined order.

Adam