Re: Order by effect on temp tables

Beware that a SELECT from a table without an ORDER BY clause might not return
data in the order in which it was inserted if the dbspace is mirrored (in
Informix rather than the operating system), as the query optimizer may decide to
read from both copies in parallel, as I know to my cost!
--
Regards,
Doug Lawry
www.douglawry.webhop.org


"Krishna" <calvinkrishy@xxxxxxxxx> wrote in message
news:1179240100.864989.136510@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
While mentioning the 'Restrictions on the Insert Selection' the IDS
documentation mentions
that: "the lack of an ORDER BY clause is not important. If you need
to ensure that the new rows are physically ordered in the table, you
can first select them into a temporary table and order it, and then
insert from the temporary table."

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlt.doc/sqltmst139.htm

Does it mean that if I select values into a temp table using an order
by clause and then read from the temp
table the order is guaranteed to be the same.

Considering the following data:
Employee table:
Name-Age
A-10
B-20
C-11

Largest table:
Name
K

==
Select name
from employee
order by age desc -- Does this ensure the temp table will have the
results in 'proper' order even while selecting?
into temp t;

update largest
set name = (select first 1 name from t);

insert into largest
select skip 1 name from t;

Would executing the above *guarantee* that the result will be the
following *always*?

Name
B
C
A



.

Relevant Pages

  • RE: Order by effect on temp tables
    ... Informix engine I have used. ... Beware that a SELECT from a table without an ORDER BY clause might not ... Does it mean that if I select values into a temp table using an order ... Would executing the above *guarantee* that the result will be the ...
    (comp.databases.informix)
  • Re: Duplicate values
    ... append the data from your initial table to this temp table with an ORDER BY clause. ... Jet respects that order by clause. ... I know i need the record more recent of the duplicates. ... I like to have, in final, my table: PVENDAS ...
    (microsoft.public.access.queries)
  • Re: Order by effect on temp tables
    ... "the lack of an ORDER BY clause is not important. ... Does it mean that if I select values into a temp table using an order ... set name = FROM employee); ... guarantee that the data will be returned from the ultimate table in sorted order if you do not include an order by clause. ...
    (comp.databases.informix)
  • Order by effect on temp tables
    ... While mentioning the 'Restrictions on the Insert Selection' the IDS ... "the lack of an ORDER BY clause is not important. ... Does it mean that if I select values into a temp table using an order ... Employee table: ...
    (comp.databases.informix)
  • Re: Row Order
    ... to exist in the temp table in the order specified by the ORDER BY clause"? ... Someone told me that there was no guarantee that the rows would get inserted ... SELECT city, state ...
    (microsoft.public.sqlserver.programming)