Row Order

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Guadala Harry (GMan_at_NoSpam.com)
Date: 11/02/04 

Date: Tue, 2 Nov 2004 10:06:13 -0800

I'm inserting rows into a temp table via a SELECT statement that has an
ORDER BY clause because I need for the rows to exist in the temp table in
the order specified by the ORDER BY clause.

The temp table is just a few varchar and int columns with an IDENTITY
property and NO indexes.

Someone told me that there was no guarantee that the rows would get inserted
into the temp table in the order specified by the ORDER BY Clause.

Is this true? If so, why - and what can be done to "guarantee" that the row
ordering corresonds to the order returned by the SELECT statement?

Here's what the setup looks like to which the person was commenting:

USE PUBS
CREATE TABLE #TEMP_TABLE
(
    RowNumber [int] IDENTITY,
    City varchar(40),
    State varchar(40)
)

INSERT INTO #TEMP_TABLE (city, state)
    SELECT city, state
    FROM stores
    ORDER BY stor_id

Thanks.

Relevant Pages

  • 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)
  • 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)
  • Re: select non-dup records
    ... Use a coordinated subquery in the where clause ... FROM YourTable as Temp ... Use a subquery in the FROM clause (if this works it will be ... Sort decending by sem and group all other ...
    (microsoft.public.access.queries)