>> Is there any significance or reliability in putting an ORDER BY

"Joe Celko" <jcelko212@earthlink.net> wrote in message
news:eN0oVKWeEHA.2044@TK2MSFTNGP10.phx.gbl...
> >> Is there any significance or reliability in putting an ORDER BY
> clause in the "derived table" of an INSERT INTO statement? <<
>
> It is a good way to tell that someone was asleep in their basic
> relational database class and does not understand that tables have no
> ordering by definition.
>
> I use it as an indicator that the rest of their code is also screwed up
> from **fundamental** misunderstandings of RDBMS when I am doing
> forensics. You just know that they are about to confuse rows and
> records -- Yep! There it is :)!
>
> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

"User" <User@aol.com> wrote in message
news:OKQ9uaSeEHA.4092@TK2MSFTNGP10.phx.gbl...
> Is there any significance or reliability in putting an ORDER BY clause in
> the "derived table" of an INSERT INTO statement?
>
> In the example below:
> #A is a table with an identity column representing the order in which
> entries were made into table #A
> If these records are copied to #B with a INSERT INTO ... SELECT FROM #A ..
> ORDER BY ... statement,
>
> Will
> SELECT item FROM #A ORDER BY seqno
> return records in the same order as
> SELECT item FROM #B ORDER BY seqno
>
>
> ====================
>
> CREATE TABLE #A(
> item varchar(8),
> seqno int IDENTITY(1,1) not null
> )
>
> CREATE TABLE #B(
> item varchar(8),
> seqno int IDENTITY(1,1) not null
> )
>
> INSERT INTO #A(item) VALUES('A')
> INSERT INTO #A(item) VALUES('B')
> INSERT INTO #A(item) VALUES('C')
> INSERT INTO #A(item) VALUES('D')
>
> INSERT INTO #B(item) SELECT item FROM #A ORDER BY seqno
>
> SELECT item FROM #A ORDER BY seqno
> SELECT item FROM #B ORDER BY seqno
>
> DROP TABLE #A
> DROP TABLE #B
>
>