Re: how to detect records order in a table with no id, timestamp?

L. Tseng wrote:
>
> John,
>
> Sorry for the confusion. Let me give an example:
>
> insert into testtable values (2,b);
> insert into testtable values (3,c);
> insert into testtable values (1,a);
>
> then, select * from testtable would look like this
>
> 2 b
> 1 a
> 3 c
>
> or
> 3 c
> 1 a
> 2 b
>
> or
> 1 a
> 2 b
> 3 c
>
> or the way they were inserted
> 2 b
> 3 c
> 1 a
>
> Without any other information availble, how can you tell the order of the insertion
> from select output?
>
>
You can't.

If you want to determine the order of insertion you *must* use a serial identifier, such as the insert date-time (sysdate) or a sequentially-generated number.

Relational theory states that *all* information about a set of rows must be in the form of user-specified data.

BTW, someone will tell you that, without an ORDER BY clause, rows are returned in order of insertion. This is not true if you have released any space due to row deletions or if you have done any re-organisation via, say, EXP/IMP.
It *may* be true if you have *never* done anything to the table except insert rows which always fit into a single page, but don't bet on it!

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"