Subject Re: [ib-support] Order of inserts
Author Helen Borrie
At 12:07 PM 09-08-02 +0000, you wrote:
>I have e table in which I have inserted a lots of records.
>Sometimes I have to access some of them in the exact order they were
>inserted in.
>If I just do a "select * from Table where MyField=:PMyField" can I
>then be sure that the fields returned to me are in the exact same
>order as they were inserted in ?
>I have done some test, and it seems this way, but I would like to be

If you have merely inserted rows and nobody has performed any further
operations on them, then they will probably be output in insertion order -
or, rather, in commit order.

But don't depend on this in real life, since the optimizer is likely to
vary its query plan according to several factors, including indexes, number
of rows, state of indexes, etc. Also, the RDB$DB_Key, which will determine
"natural order" in the absence of other compelling influences, varies over

>Of course I could put on a Autoincrement field to solve my problem,
>but I havn't one now, and would like to solve my problem without
>doing this.

That's how you'll need to do it and use ORDER BY to get the output in
insertion order. Alternatively, you could index a timestamp column that
records the current time by way of a BEFORE INSERT trigger:

create trigger....
new.time_stamp = cast('NOW' as timestamp);


All for Open and Open for All
Firebird Open SQL Database · ·