Re: Order by effect on temp tables

Krishna wrote:
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.

No.

First, you cannot include an ORDER BY clause in a SELECT statement that includes an INTO TEMP clause. The doc you quote is suggesting that you:

SELECT name
FROM employee
INTO TEMP t;

SELECT name
FROM t
ORDER BY name DESC;

K
C
B
A

-- OR order the data in the temp table with:

CREATE CLUSTER INDEX t.idx ON t(name DESC);
SELECT name
FROM t;

K
C
B
A

But, see below, you MAY not be able to depend on the cluster ordering.

Of course, either way, you COULD:

update largest
set name = (SELECT FIRST 1 name
FROM t
ORDER BY name DESC);

--or why not just simply--

update largest
set name = (SELECT MAX( name ) FROM employee);

Even if you selected from the temp table ORDER BY and inserted into an existing table using INSERT INTO ... SELECT ... ORDER BY... there's no guarantee that the data will be returned from the ultimate table in sorted order if you do not include an order by clause. ANSI SQL explicitely requires that the order of data returned is NOT guaranteed unless an ORDER BY clause is included and most modern optimizers and multi-threaded RDBMSes take advantage of that to improve performance. Yes, data that's been inserted ordered (or clustered into some specific order) will TEND to be returned in the same order, but there's nothing that says it always will be.

Art S. Kagel

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)
  • 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)
  • Re: Order by effect on temp tables
    ... Beware that a SELECT from a table without an ORDER BY clause might not return ... Does it mean that if I select values into a temp table using an order ... Employee table: ... Would executing the above *guarantee* that the result will be the ...
    (comp.databases.informix)