> I have a table that is ordered by a date and a time field. The users
> do not enter the date and time, but they do fill in other information
> on the record. As they do, and after I refresh the view, records with
> the same date and time sometimes seem to be returned in a different
> order.
>
> Is it possible that Firebird is returning the records is a different
> order since it has no instructions to the contrary? Do I need to
> include the record ID in the "order by" to stop this? For example,
> ORDER BY MyDate,MyTime,MyRecordID? Is there a better way?
>

The standard says that if you do not specify an order by clause then
the records can be returned in any order. Further if you do have an
order by clause, and two records are equal in terms of that order by
clause then those two records then the records can be returned in any
order.

To look at it another way, if you do not explicitly state the order
you want the records to be returned in, then Firebird will return them
in the most convenient order.

Assume the following table:

Test

ID Field1 Field2
1 1 1
2 1 2
3 2 1

'select id from test' will return one of the following

1/2/3
1/3/2
2/1/3
2/3/1
3/1/2
3/2/1

'select id from test order by field1' will return one of the following

1/2/3
2/1/3

'select id from test order by field1, field2' will always return

1/2/3

It is not a conspiracy from Firebird to randomise the record order if
you do not say so, the reasoning is actually pretty simple. If I don't
care which order the results are returned, why should Firebird spend
time sorting them a particular way? If I don't care, it means do it in
the fastest way you possibly can.

Records are not stored in order of their primary key as perhaps in
some other databases. In fact the relational model itself says that
the data has no particular order. Implementation-wise, Firebird
normally** stores records chronologically. Of course if you start
deleting records, then you may find that your records will instead
re-use the space the deleted records formerly occupied. Lesson: Do not
expect a plain vanilla select without an explicit order to return the
records in the same order.

Now if you wanted to use an exact timestamp, Helen's post explains
that pretty well. If you just wanted to ensure a consistent order was
returned, then including the PK field(s) will guarantee this. Of
course this is not strictly chronological but rather chronological in
the order the PK values were handed out by the generator.

Adam