Re: Question related to inserts

I believe Oracle (or any other databases) does not guarantee the order of rows returned in an SQL statement that does not contain an order by clause. Just place an order by on the select statement when retrieving from the scratch table.

select .. from scratch order by pcode

The way the database holds the rows internally is up to it.

PMG wrote:

> I have a question relating to inserting into a table.
>
> Is it GUARANTEED that the row order is maintained in the same order that
> the rows are inserted into a table after a commit?
>
> I have a table (which I intend to use as a scratch pad). It has no
> primary key, no indexes, nothing but a bunch of columns.
>
> I have created a query (something like 'Select distinct product_code
> from product_table order by 1 desc').
>
> I would like to insert the results of the query into this table. I get
> an error message when I do
> 'insert into scratch_pad (fld1) select distinct product_code from
> product_table order by 1 desc'
> because of the order by clause. Fine, I can live with that.
>
> So, using Delphi as the front-end, what I tried to is 1). create the
> query; 2).iterate through each row of the query from the first row to
> the last; and 3). insert each row into the table.
>
> What I am noticing is that, after commit, the rows are not always in
> the same, (in this case descending product code) order as the query
> results, even though I know that each row is being processed in the
> correct order. Sometimes they are, and sometimes they are not. Any
> ideas, or suggestions for an alternate solution.
>
> TIA
>
> Pete
Received on Tue Jan 19 1999 - 05:04:38 CST