Thanks Alan and Adam.

The "Natural ordering" has been identified with the order a table's
records happen to be on disk, and if an "Order By" is missing from a
select statement then the select statement will act as if "order by
Natural" was written, understanding there is no "natural" ordering
that can be used in a statement. Is this correct?

The natural ordering of a table is affected only by insertions and
deletions on the table. Is this correct?

It was suggested that immediately following a backup and restore the
natural ordering is the primary key ordering. Is this correct?

I had a misunderstanding in my thinking that an unspecified order
assumed the primary key order. That I understand now is wrong.

Thanks
Russell

--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...>
wrote:

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

primary

> key field.
>
> In a stored procedure I use
> select FIRST 1 FIELD_PK from ATABLE
> where FIELD2 > 0
>
> Can I be certain that this selection is the same as
> select FIRST 1 FIELD_PK from ATABLE
> where FIELD2 > 0
> order by FIELD_PK?
>
>
> NO. The SP select statement does not use an ORDER BY clause and

therefore

> NATURAL order will be used. That means that the record (s) will be

retrieved

> in order of where they are on the data page. Since records can be

deleted

> and their space taken up again by older records, an newer record

(higher PK)

> may well appear before an older record (lower PK).
> Perhaps straight after a backup/restore cycle, you may see natural

order the

> same as PK order but there are no guarantees within the

implementation.

> That's why there is an ORDER BY clause in the SQL standard.
> Alan
>