Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 7 of 7 total |
Create table with ORDER BY. |
Tue, Oct 2 2007 2:02 PM | Permanent Link |
Abdulaziz Jasser | 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? |
Tue, Oct 2 2007 3:09 PM | Permanent Link |
Bill Mullen | >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. |
Tue, Oct 2 2007 3:51 PM | Permanent Link |
Abdulaziz Jasser | 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? |
Tue, Oct 2 2007 5:18 PM | Permanent Link |
Bill Mullen | >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. |
Tue, Oct 2 2007 5:28 PM | Permanent Link |
Abdulaziz Jasser | 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. |
Tue, Oct 2 2007 5:45 PM | Permanent Link |
Bill Mullen | >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. |
Tue, Oct 2 2007 6:15 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |