In the following SQL the "ORDER BY" clause in not respected when createing the table with the data:

CREATE TABLE tbSwitches AS SELECT * FROM MyDatabase.TB_BranchSwitchs WHERE BranchSysNo = 1 ORDER BY SwitchNo WITH DATA ';

To solve this issue: I create the table first and then fill it with data using a separate SQL statement:

CREATE TABLE tbSwitches AS SELECT * FROM MyDatabase.TB_BranchSwitchs WITH NO DATA;

INSERT INTO tbSwitches SELECT * FROM MyDatabase.TB_BranchSwitchs WHERE BranchSysNo = 1 ORDER BY SwitchNo;

Is this a normal behavior for the "ORDER BY" clause when creating a table?

>In the following SQL the "ORDER BY" clause in not respected when createing the table with the data:
>
>CREATE TABLE tbSwitches AS SELECT * FROM MyDatabase.TB_BranchSwitchs WHERE BranchSysNo = 1 ORDER BY SwitchNo WITH DATA ';
>
>To solve this issue: I create the table first and then fill it with data using a separate SQL statement:
>
>CREATE TABLE tbSwitches AS SELECT * FROM MyDatabase.TB_BranchSwitchs WITH NO DATA;
>
>INSERT INTO tbSwitches SELECT * FROM MyDatabase.TB_BranchSwitchs WHERE BranchSysNo = 1 ORDER BY SwitchNo;
>
>Is this a normal behavior for the "ORDER BY" clause when creating a table?

Order By is used to order the result set of a select not to insert
data into the database in an data ordered manner.  Data is not
normally stored sorted in the database but you can create indexes so
that the speed of your select states are improved.  

Bill,

<<Order By is used to order the result set of a select not to insert
data into the database in an data ordered manner.  Data is not
normally stored sorted in the database but you can create indexes so
that the speed of your select states are improved.>>

Thanks for the comment.  However I have to disagree with you.  When I used the second SQL (INSERT) it inserts the data according to the ORDER
BY clause.  How do you explain that?

>Bill,
>
><<Order By is used to order the result set of a select not to insert
>data into the database in an data ordered manner.  Data is not
>normally stored sorted in the database but you can create indexes so
>that the speed of your select states are improved.>>
>
>Thanks for the comment.  However I have to disagree with you.  When I used the second SQL (INSERT) it inserts the data according to the ORDER
>BY clause.  How do you explain that?


Because the data was selected and Order By ordered the result of the
data that was selected and then the data was inserted.

Bill,

<<Because the data was selected and Order By ordered the result of the
data that was selected and then the data was inserted. >>

The same thing should (I think) apply on CREATE TABLE...WITH DATA.

>Bill,
>
><<Because the data was selected and Order By ordered the result of the
>data that was selected and then the data was inserted. >>
>
>The same thing should (I think) apply on CREATE TABLE...WITH DATA.

I would tend to agree, however, the "AS" subquery clause in the table
definition is an optional feature of SQL:2003 and Tim may have elected
to ignore the ORDER BY clause of the sub query in this particular
area.  

Abdulaziz,

<< Is this a normal behavior for the "ORDER BY" clause when creating a
table? >>

Actually, the ORDER BY clause shouldn't be allowed in such a case since it
really isn't part of the standard, and I'll probably remove the capability
shortly.   For the record, EDB is internally creating an index that allows
the table to be sorted on the ORDER BY clause - it's just that it isn't
visible to you, the user.

--
Tim Young
Elevate Software
www.elevatesoft.com