Subject Re: FIRST 1 question
Author russellbelding
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.


--- In, "Alan McDonald" <alan@...>
> In the database is
> ATable with fields FIELD_PK, FIELD2, .... and FIELD_PK is the
> 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
> NATURAL order will be used. That means that the record (s) will be
> in order of where they are on the data page. Since records can be
> 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
> That's why there is an ORDER BY clause in the SQL standard.
> Alan