db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From chaa...@apache.org
Subject svn commit: r904155 - in /db/derby/docs/trunk/src: ref/ tuning/
Date Thu, 28 Jan 2010 16:54:19 GMT
Author: chaase3
Date: Thu Jan 28 16:54:18 2010
New Revision: 904155

URL: http://svn.apache.org/viewvc?rev=904155&view=rev
Log:
DERBY-4518: Document ORDER BY and OFFSET/FETCH in subqueries

Made changes to 11 files in Reference Manual and Tuning Derby.

Patch: DERBY-4518-3.diff

Modified:
    db/derby/docs/trunk/src/ref/rrefscalarsubquery.dita
    db/derby/docs/trunk/src/ref/rrefselectexpression.dita
    db/derby/docs/trunk/src/ref/rrefsqlj11277.dita
    db/derby/docs/trunk/src/ref/rrefsqlj13658.dita
    db/derby/docs/trunk/src/ref/rrefsqlj15446.dita
    db/derby/docs/trunk/src/ref/rrefsqlj21571.dita
    db/derby/docs/trunk/src/ref/rrefsqlj40774.dita
    db/derby/docs/trunk/src/ref/rreftablesubquery.dita
    db/derby/docs/trunk/src/tuning/ctuntransform22576.dita
    db/derby/docs/trunk/src/tuning/ctuntransform25868.dita
    db/derby/docs/trunk/src/tuning/ctuntransform36368.dita

Modified: db/derby/docs/trunk/src/ref/rrefscalarsubquery.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefscalarsubquery.dita?rev=904155&r1=904154&r2=904155&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefscalarsubquery.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefscalarsubquery.dita Thu Jan 28 16:54:18 2010
@@ -28,7 +28,13 @@
 into a scalar value because it returns only a single row and column value.</p> <p>The
 query must evaluate to a single row with a single column.</p> <p><indexterm>Expression
 subquery</indexterm>Sometimes also called an expression subquery.</p></section>
-<refsyn><title>Syntax</title> <codeblock><b>(<i><xref
href="rrefsqlj21571.dita#rrefsqlj21571">Query</xref></i>)</b></codeblock>
</refsyn>
+<refsyn><title>Syntax</title>
+<codeblock><b>( <i><xref href="rrefsqlj21571.dita#rrefsqlj21571">Query</xref></i>
+    [ <i><xref href="rrefsqlj13658.dita#rrefsqlj13658">ORDER BY clause</xref></i>
]
+    [ <i><xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">result offset
clause</xref></i> ]
+    [ <i><xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">fetch first
clause</xref></i> ]
+)</b></codeblock>
+</refsyn>
 <example><title>Examples</title><codeblock><b><ph>--
avg always returns a single value, so the subquery is
 -- a ScalarSubquery</ph>
 SELECT NAME, COMM

Modified: db/derby/docs/trunk/src/ref/rrefselectexpression.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefselectexpression.dita?rev=904155&r1=904154&r2=904155&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefselectexpression.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefselectexpression.dita Thu Jan 28 16:54:18 2010
@@ -29,10 +29,15 @@
 used to build a table value based on filtering and projecting values from
 other tables.</p></section>
 <refsyn><title>Syntax</title> <codeblock><b>SELECT [ DISTINCT
| ALL ] <i>SelectItem</i> [ , <i>SelectItem </i>]*
-<i><xref href="rrefsqlj21583.dita#rrefsqlj21583"></xref></i>
+<i><xref href="rrefsqlj21583.dita#rrefsqlj21583">FROM clause</xref></i>
 [ <i><xref href="rrefsqlj33602.dita#rrefsqlj33602">WHERE clause</xref>
</i>]
 [ <i><xref href="rrefsqlj32654.dita#rrefsqlj32654">GROUP BY clause</xref></i>
]
-[ <i><xref href="rrefsqlj14854.dita#rrefsqlj14854">HAVING clause</xref></i>
]</b></codeblock> <p><b>SelectItem:</b></p> <codeblock><b>{
+[ <i><xref href="rrefsqlj14854.dita#rrefsqlj14854">HAVING clause</xref></i>
]
+[ <i><xref href="rrefsqlj13658.dita#rrefsqlj13658">ORDER BY clause</xref></i>
]
+[ <i><xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">result offset
clause</xref></i> ]
+[ <i><xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">fetch first clause</xref></i>
]
+</b></codeblock>
+<p><b>SelectItem:</b></p> <codeblock><b>{
     * |
     { <i><xref href="rreftablename.dita#rreftablename">table-Name</xref></i>
| <i><xref
 href="rrefcorrelationname.dita#rrefcorrelationname">correlation-Name</xref></i>
} .* |
@@ -55,9 +60,10 @@
 there is a GROUP BY clause, the SELECT clause must contain <i>only</i> aggregates
 or grouping columns. If you want to include a non-grouped column in the SELECT
 clause, include the column in an aggregate expression. For example:   <codeblock><b>--
List head count of each department, 
--- the department number (WORKDEPT), and the average departmental salary (SALARY) 
--- for all departments in the EMPLOYEE table. 
--- Arrange the result table in ascending order by average departmental salary.
+-- the department number (WORKDEPT), and the average departmental salary
+-- (SALARY) for all departments in the EMPLOYEE table. 
+-- Arrange the result table in ascending order by average departmental
+-- salary.
 SELECT COUNT(*),WORK_DEPT,AVG(SALARY)
      FROM EMPLOYEE
      GROUP BY WORK_DEPT
@@ -70,12 +76,24 @@
 table. If the HAVING clause evaluates to TRUE, the row is retained for further
 processing. If the HAVING clause evaluates to FALSE or NULL, the row is discarded.
 If there is a HAVING clause but no GROUP BY, the table is implicitly grouped
-into one group for the entire table.</p> <p><ph conref="../conrefs.dita#prod/productshortname"></ph>
processes
+into one group for the entire table.</p>
+<p>The ORDER BY clause allows you to specify the order in which rows appear in
+the result set. In subqueries, the ORDER BY clause is meaningless unless it is
+accompanied by one or both of the result offset and fetch first clauses or in
+conjunction with the ROW_NUMBER function.</p>
+<p>The result offset clause provides a way to skip the N first rows in a result
+set before starting to return any rows. The fetch first clause, which can be
+combined with the result offset clause if desired, limits the number of rows
+returned in the result set.</p>
+<p><ph conref="../conrefs.dita#prod/productshortname"></ph> processes
 a <i>SelectExpression</i> in the following order:   <ul>
 <li>FROM clause</li>
 <li>WHERE clause</li>
 <li>GROUP BY (or implicit GROUP BY)</li>
 <li>HAVING clause</li>
+<li>ORDER BY clause</li>
+<li>Result offset clause</li>
+<li>Fetch first clause</li>
 <li>SELECT clause</li>
 </ul></p> <p>The result of a <i>SelectExpression</i> is always
a table.</p> <p>When
 a query does not have a FROM clause (when you are constructing a value, not
@@ -97,30 +115,33 @@
 <li>They are used in the ORDER BY clause as the column names available for
 sorting.</li>
 </ul></p> </section>
-<example> <codeblock><b><ph>-- this example shows SELECT-FROM-WHERE
+<example><title>Examples</title>
+<codeblock><b><ph>-- This example shows SELECT-FROM-WHERE
 -- with an ORDER BY clause
--- and correlation-Names for the tables</ph>
+-- and correlation-Names for the tables.</ph>
 SELECT CONSTRAINTNAME, COLUMNNAME 
-FROM SYS.SYSTABLES t, SYS.SYSCOLUMNS col,
-SYS.SYSCONSTRAINTS cons, SYS.SYSCHECKS checks 
-WHERE t.TABLENAME = 'FLIGHTS' AND t.TABLEID = col.
-REFERENCEID AND t.TABLEID = cons.TABLEID 
-AND cons.CONSTRAINTID = checks.CONSTRAINTID 
-ORDER BY CONSTRAINTNAME
+    FROM SYS.SYSTABLES t, SYS.SYSCOLUMNS col,
+    SYS.SYSCONSTRAINTS cons, SYS.SYSCHECKS checks 
+  WHERE t.TABLENAME = 'FLIGHTS' 
+    AND t.TABLEID = col.REFERENCEID 
+    AND t.TABLEID = cons.TABLEID 
+    AND cons.CONSTRAINTID = checks.CONSTRAINTID 
+  ORDER BY CONSTRAINTNAME
 <ph>-- This example shows the use of the DISTINCT clause</ph>
 SELECT DISTINCT ACTNO
-FROM EMP_ACT
-<ph>-- This example shows how to rename an expression 
+    FROM EMP_ACT
+<ph>-- This example shows how to rename an expression
 -- Using the EMPLOYEE table, list the department number (WORKDEPT) and 
 -- maximum departmental salary (SALARY) renamed as BOSS 
 -- for all departments whose maximum salary is less than the 
 -- average salary in all other departments.</ph>
 SELECT WORKDEPT AS DPT, MAX(SALARY) AS BOSS 
-	FROM EMPLOYEE EMP_COR 
-	GROUP BY WORKDEPT 
-	HAVING MAX(SALARY) &lt; (SELECT AVG(SALARY)
-				FROM EMPLOYEE
-				WHERE NOT WORKDEPT = EMP_COR.WORKDEPT) 
-	ORDER BY BOSS</b></codeblock> </example>
+    FROM EMPLOYEE EMP_COR 
+    GROUP BY WORKDEPT 
+    HAVING MAX(SALARY) &lt; (SELECT AVG(SALARY)
+                          FROM EMPLOYEE
+                          WHERE NOT WORKDEPT = EMP_COR.WORKDEPT) 
+    ORDER BY BOSS</b>
+</codeblock> </example>
 </refbody>
 </reference>

Modified: db/derby/docs/trunk/src/ref/rrefsqlj11277.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqlj11277.dita?rev=904155&r1=904154&r2=904155&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqlj11277.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsqlj11277.dita Thu Jan 28 16:54:18 2010
@@ -38,7 +38,10 @@
     VALUES ( <i>Value</i> {, <i>Value</i> }* )
         [ , ( <i>Value</i> {, <i>Value</i> }* ) ]* |
     VALUES <i>Value</i> [ , <i>Value</i> ]*
- }</b></codeblock> 
+} [ <i><xref href="rrefsqlj13658.dita#rrefsqlj13658">ORDER BY clause</xref></i>
]
+  [ <i><xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">result offset
clause</xref></i> ]
+  [ <i><xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">fetch first
clause</xref></i> ]
+</b></codeblock> 
 <p>where <i>Value</i> is defined as</p>
 <codeblock><b><i>Expression</i> | DEFAULT</b></codeblock>
 <p>The first form constructs multi-column rows. The second
@@ -47,7 +50,13 @@
 expression is in an INSERT statement. Specifying DEFAULT for a column inserts
 the column's default value into the column. Another way to insert the default
 value into the column is to omit the column from the column list and only
-insert values into other columns in the table.</p> </refsyn>
+insert values into other columns in the table.</p>
+<p>A VALUES expression that is used in an INSERT statement cannot use an
+ORDER BY, result offset, or fetch first clause. However, if the VALUES
+expression does not contain the DEFAULT keyword, the VALUES clause can be put
+in a subquery and ordered, as in the following statement:</p>
+<codeblock><b>INSERT INTO t SELECT * FROM (VALUES 'a','c','b') t ORDER BY 1;</b></codeblock>
+</refsyn>
 <example><title>Examples</title><codeblock><b><ph>--
3 rows of 1 column</ph>
 VALUES (1),(2),(3)
 <ph>-- 3 rows of 1 column</ph>
@@ -56,6 +65,10 @@
 VALUES (1, 2, 3)
 <ph>-- 3 rows of 2 columns</ph>
 VALUES (1,21),(2,22),(3,23)
+<ph>-- using ORDER BY and FETCH FIRST</ph>
+VALUES (3,21),(1,22),(2,23) ORDER BY 1 FETCH FIRST 2 ROWS ONLY
+<ph>-- using ORDER BY and OFFSET</ph>
+VALUES (3,21),(1,22),(2,23) ORDER BY 1 OFFSET 1 ROW
 <ph>-- constructing a derived table</ph>
 VALUES ('orange', 'orange'), ('apple', 'red'),
 ('banana', 'yellow')

Modified: db/derby/docs/trunk/src/ref/rrefsqlj13658.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqlj13658.dita?rev=904155&r1=904154&r2=904155&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqlj13658.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsqlj13658.dita Thu Jan 28 16:54:18 2010
@@ -20,9 +20,6 @@
 -->
 <reference id="rrefsqlj13658" xml:lang="en-us">
 <title>ORDER BY clause</title>
-<shortdesc>The ORDER BY clause is an optional element of a SELECT statement.
-An ORDER BY clause allows you to specify the order in which rows appear in
-the <varname>ResultSet</varname>.</shortdesc>
 <prolog><metadata>
 <keywords><indexterm>ORDER BY clause<indexterm>syntax</indexterm></indexterm>
 <indexterm>ASC<indexterm>implied in ORDER BY clause</indexterm></indexterm>
@@ -32,6 +29,26 @@
 </keywords>
 </metadata></prolog>
 <refbody>
+<section><p>The ORDER BY clause is an optional element of the following:
+<ul>
+<li>A <xref href="rrefsqlj41360.dita#rrefsqlj41360">SELECT statement</xref></li>

+<li>A <i><xref href="rrefselectexpression.dita#rrefselectexpression"></xref></i></li>
+<li>A <xref href="rrefsqlj11277.dita#rrefsqlj11277">VALUES expression</xref></li>
+<li>A <i><xref href="rrefscalarsubquery.dita#rrefscalarsubquery"></xref></i></li>
+<li>A <i><xref href="rreftablesubquery.dita#rreftablesubquery"></xref></i></li>

+</ul></p>
+<p>It can also be used in an
+<xref href="rrefsqlj40774.dita#rrefsqlj40774">INSERT statement</xref> or a
+<xref href="rrefsqlj15446.dita#rrefsqlj15446">CREATE VIEW statement</xref>.</p>
+<p>An ORDER BY clause allows you to specify the order in which rows appear in
+the result set. In subqueries, the ORDER BY clause is meaningless unless it is
+accompanied by one or both of the
+<xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">result offset and
+fetch first clauses</xref> or in conjunction with the
+<xref href="rreffuncrownumber.dita#rreffuncrownumber"></xref>, since there is
no
+guarantee that the order is retained in the outer result set. It is permissible
+to combine ORDER BY on the outer query with ORDER BY in subqueries.</p>
+</section>
 <refsyn><title>Syntax</title><codeblock><b>ORDER BY { <i><xref
href="rrefcolumnname.dita#rrefcolumnname"></xref></i> | <i>ColumnPosition</i>
| <i
 >Expression</i> }
     [ ASC | DESC ]

Modified: db/derby/docs/trunk/src/ref/rrefsqlj15446.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqlj15446.dita?rev=904155&r1=904154&r2=904155&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqlj15446.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsqlj15446.dita Thu Jan 28 16:54:18 2010
@@ -52,12 +52,15 @@
 <refsyn><title>Syntax</title> <codeblock><b>CREATE VIEW <i><xref
href="rrefviewname.dita#rrefviewname">view-Name</xref></i>
     [ ( <i><xref href="rrefsimplecolumnname.dita#rrefsimplecolumnname">Simple-column-Name</xref></i>
[, <i><xref
 href="rrefsimplecolumnname.dita#rrefsimplecolumnname">Simple-column-Name</xref></i>]
* ) ]
-AS <i><xref href="rrefsqlj21571.dita#rrefsqlj21571">Query</xref></i>
</b></codeblock> <p>A
+AS <i><xref href="rrefsqlj21571.dita#rrefsqlj21571">Query</xref></i>
[ <i><xref href="rrefsqlj13658.dita#rrefsqlj13658">ORDER BY clause</xref></i>
]
+         [ <i><xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">result
offset clause</xref></i> ]
+         [ <i><xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">fetch
first clause</xref></i> ]
+</b></codeblock> <p>A
 view definition can contain an optional view column list to explicitly name
 the columns in the view. If there is no column list, the view inherits the
 column names from the underlying query. All columns in a view must be uniquely
 named.</p> </refsyn>
-<example> <codeblock><b>CREATE VIEW SAMP.V1 (COL_SUM, COL_DIFF)
+<example><title>Examples</title> <codeblock><b>CREATE VIEW
SAMP.V1 (COL_SUM, COL_DIFF)
 	AS SELECT COMM + BONUS, COMM - BONUS
 	FROM SAMP.EMPLOYEE;
 

Modified: db/derby/docs/trunk/src/ref/rrefsqlj21571.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqlj21571.dita?rev=904155&r1=904154&r2=904155&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqlj21571.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsqlj21571.dita Thu Jan 28 16:54:18 2010
@@ -26,11 +26,15 @@
 <section> <p>A query creates a virtual table based on existing tables or constants
 built into tables.</p></section>
 <refsyn><title>Syntax</title><codeblock><b>{
-    ( <i>Query</i> ) |
-<i>    Query</i> INTERSECT [ ALL | DISTINCT ] <i>Query</i> |
-<i>    Query</i> EXCEPT [ ALL | DISTINCT ] <i>Query</i> |
-<i>    Query</i> UNION [ ALL | DISTINCT ] <i>Query</i> |
-<i>    <xref href="rrefselectexpression.dita#rrefselectexpression">SelectExpression</xref></i>
| <i><xref
+    ( <i>Query</i> 
+         [ <i><xref href="rrefsqlj13658.dita#rrefsqlj13658">ORDER BY clause</xref></i>
]
+         [ <i><xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">result
offset clause</xref></i> ]
+         [ <i><xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">fetch
first clause</xref></i> ]
+    ) |
+    <i>Query</i> INTERSECT [ ALL | DISTINCT ] <i>Query</i> |
+    <i>Query</i> EXCEPT [ ALL | DISTINCT ] <i>Query</i> |
+    <i>Query</i> UNION [ ALL | DISTINCT ] <i>Query</i> |
+    <i><xref href="rrefselectexpression.dita#rrefselectexpression">SelectExpression</xref></i>
| <i><xref
 href="rrefsqlj11277.dita#rrefsqlj11277">VALUES Expression</xref></i>
 }</b></codeblock><p>You can arbitrarily put parentheses around queries,
or
 use the parentheses to control the order of evaluation of the INTERSECT, EXCEPT,
@@ -52,7 +56,7 @@
 row that the output table contains (assuming the ALL keyword is specified)
 is:<ul>
 <li>UNION: ( L + R ).</li>
-<li>EXCEPT: the maximum of ( L – R ) and 0 (zero).</li>
+<li>EXCEPT: the maximum of ( L - R ) and 0 (zero).</li>
 <li>INTERSECT: the minimum of L and R.</li>
 </ul></p></section>
 <example><title>Examples</title><codeblock><b><ph>--
a Select expression </ph>
@@ -78,9 +82,19 @@
 VALUES (1,2), (3,4)
 
 <ph>-- a values expression</ph>
-VALUES (1,2,3)</b>
-<b>-- List the employee numbers (EMPNO) of all employees in the EMPLOYEE table 
--- whose department number (WORKDEPT) either begins with 'E' or 
+VALUES (1,2,3)
+
+<ph>-- Use of ORDER BY and FETCH FIRST in a subquery</ph>
+SELECT DISTINCT A.ORIG_AIRPORT, B.FLIGHT_ID FROM 
+   (SELECT FLIGHT_ID, ORIG_AIRPORT 
+       FROM FLIGHTS 
+       ORDER BY ORIG_AIRPORT DESC 
+       FETCH FIRST 40 ROWS ONLY) 
+    AS A, FLIGHTAVAILABILITY AS B 
+   WHERE A.FLIGHT_ID = B.FLIGHT_ID</b>
+   
+<b>-- List the employee numbers (EMPNO) of all employees in the EMPLOYEE 
+-- table whose department number (WORKDEPT) either begins with 'E' or 
 -- who are assigned to projects in the EMP_ACT table 
 -- whose project number (PROJNO) equals 'MA2100', 'MA2110', or 'MA2112'
 SELECT EMPNO
@@ -112,8 +126,8 @@
      FROM EMP_ACT
      WHERE PROJNO IN('MA2100', 'MA2110', 'MA2112')</b>
 <b>-- Make the same query as in the previous example, 
--- only include an additional two employees currently not in any table and 
--- tag these rows as "new"
+-- only include an additional two employees currently not in any table
+-- and tag these rows as "new"
  SELECT EMPNO, 'emp'
      FROM EMPLOYEE
      WHERE WORKDEPT LIKE 'E%'

Modified: db/derby/docs/trunk/src/ref/rrefsqlj40774.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqlj40774.dita?rev=904155&r1=904154&r2=904155&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqlj40774.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsqlj40774.dita Thu Jan 28 16:54:18 2010
@@ -34,20 +34,32 @@
 <refsyn><title>Syntax</title> <codeblock><b>INSERT INTO table-Name
     [ (<i><xref href="rrefsimplecolumnname.dita#rrefsimplecolumnname">Simple-column-Name</xref></i>
[ , <i><xref
 href="rrefsimplecolumnname.dita#rrefsimplecolumnname">Simple-column-Name</xref></i>]*
) ]
-	  Query</b></codeblock> <p><i>Query</i> can be:   <ul>
-<li>a <i><xref href="rrefselectexpression.dita#rrefselectexpression">SelectExpression</xref></i></li>
-<li>a VALUES list</li>
-<li>a multiple-row VALUES expression   <p>Single-row and multiple-row lists
+          Query [ <i><xref href="rrefsqlj13658.dita#rrefsqlj13658">ORDER BY clause</xref></i>
]
+                [ <i><xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">result
offset clause</xref></i> ]
+                [ <i><xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">fetch
first clause</xref></i> ]
+</b></codeblock> <p><i>Query</i> can be:   <ul>
+<li>A <i><xref href="rrefselectexpression.dita#rrefselectexpression">SelectExpression</xref></i></li>
+<li>A single-row or multiple-row VALUES expression
+<p>Single-row and multiple-row VALUES expressions
 can include the keyword DEFAULT. Specifying DEFAULT for a column inserts the
 column's default value into the column. Another way to insert the default
 value into the column is to omit the column from the column list and only
-insert values into other columns in the table. For more information see <xref
-href="rrefsqlj11277.dita#rrefsqlj11277">VALUES Expression</xref>.</p>
+insert values into other columns in the table. For more information, see <xref
+href="rrefsqlj11277.dita#rrefsqlj11277"></xref>.</p>
 <p>The DEFAULT literal is the only value which you can directly
 insert into a generated column.</p></li>
 <li>UNION expressions</li>
-</ul></p> <p>For more information about Query, see <xref href="rrefsqlj21571.dita#rrefsqlj21571"></xref>.</p>
</refsyn>
-<example> <codeblock><b>INSERT INTO COUNTRIES
+</ul></p>
+<p>When you want insertion to happen with a specific ordering (for example, in
+conjunction with auto-generated keys), it can be useful to specify an ORDER BY
+clause on the result set to be inserted.</p>
+<p>If the Query is a VALUES expression, it cannot contain or be followed by an
+ORDER BY, result offset, or fetch first clause. However, if the VALUES
+expression does not contain the DEFAULT keyword, the VALUES clause can be put
+in a subquery and ordered, as in the following statement:</p>
+<codeblock><b>INSERT INTO t SELECT * FROM (VALUES 'a','c','b') t ORDER BY 1;</b></codeblock>
+<p>For more information about Query, see <xref href="rrefsqlj21571.dita#rrefsqlj21571"></xref>.</p>
</refsyn>
+<example><title>Examples</title> <codeblock><b>INSERT INTO
COUNTRIES
       VALUES ('Taiwan', 'TW', 'Asia')
 
 -- Insert a new department into the DEPARTMENT table,
@@ -81,6 +93,21 @@
 <ph>-- Insert the DEFAULT value for the LOCATION column</ph>
 INSERT INTO DEPARTMENT
       VALUES ('E31', 'ARCHITECTURE', '00390', 'E01', DEFAULT)
+
+<ph>-- Create an AIRPORTS table and insert into it
+-- some of the fields from the CITIES table, with the airport
+-- codes sorted alphabetically</ph>
+CREATE TABLE AIRPORTS (
+  AIRPORT_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
+    PRIMARY KEY,
+  AIRPORT VARCHAR(3),
+  CITY VARCHAR(24) NOT NULL,
+  COUNTRY VARCHAR(26) NOT NULL
+);
+
+INSERT INTO AIRPORTS (AIRPORT, CITY, COUNTRY)
+  SELECT AIRPORT, CITY_NAME, COUNTRY FROM CITIES
+  ORDER BY AIRPORT;
 </b></codeblock> </example>
 <section><title>Statement dependency system</title> <p>The INSERT
 statement depends on the table being inserted into, all of the conglomerates

Modified: db/derby/docs/trunk/src/ref/rreftablesubquery.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rreftablesubquery.dita?rev=904155&r1=904154&r2=904155&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rreftablesubquery.dita (original)
+++ db/derby/docs/trunk/src/ref/rreftablesubquery.dita Thu Jan 28 16:54:18 2010
@@ -39,7 +39,11 @@
 <p>When used with IN or quantified
 comparisons, it must return a single column.</p></section>
 <refsyn><title>Syntax</title>
-<codeblock><b>(<i><xref href="rrefsqlj21571.dita#rrefsqlj21571">Query</xref></i>)</b></codeblock>
+<codeblock><b>( <i><xref href="rrefsqlj21571.dita#rrefsqlj21571">Query</xref></i>
+    [ <i><xref href="rrefsqlj13658.dita#rrefsqlj13658">ORDER BY clause</xref></i>
]
+    [ <i><xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">result offset
clause</xref></i> ]
+    [ <i><xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">fetch first
clause</xref></i> ]
+)</b></codeblock>
 </refsyn>
 <example><title>Example</title><codeblock><b><ph>-- a
subquery used as a TableExpression in a FROM clause</ph>
 SELECT VirtualFlightTable.flight_ID
@@ -67,6 +71,13 @@
     (SELECT flight_ID
     FROM Flights WHERE orig_airport = 'SFO'
     OR dest_airport = 'SCL')
+<ph>-- a subquery with ORDER BY and FETCH FIRST clauses</ph>
+SELECT flight_id, segment_number
+FROM Flights
+WHERE flight_id IN
+    (SELECT flight_ID
+    FROM Flights WHERE orig_airport = 'SFO'
+    OR dest_airport = 'SCL' ORDER BY flight_id FETCH FIRST 12 ROWS ONLY)
 <ph>-- a subquery used with a quantified comparison</ph>
 SELECT NAME, COMM 
 FROM STAFF 

Modified: db/derby/docs/trunk/src/tuning/ctuntransform22576.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tuning/ctuntransform22576.dita?rev=904155&r1=904154&r2=904155&view=diff
==============================================================================
--- db/derby/docs/trunk/src/tuning/ctuntransform22576.dita (original)
+++ db/derby/docs/trunk/src/tuning/ctuntransform22576.dita Thu Jan 28 16:54:18 2010
@@ -34,6 +34,7 @@
 <li>The select list is composed entirely of <xref href="ctuntransform13966.dita#ctuntransform13966/rtuntransform13785">simple
column reference</xref>s and constants.</li>
 <li>There is no GROUP BY clause in the view. </li>
 <li>There is no DISTINCT in the view. </li>
+<li>There is no ORDER BY, result offset, or fetch first clause in the view.</li>
 </ul></p>
 <p>For example, given view <i>v1(a,b):</i>  
 <codeblock><b>SELECT Cities.city_name, Countries.country_iso_code

Modified: db/derby/docs/trunk/src/tuning/ctuntransform25868.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tuning/ctuntransform25868.dita?rev=904155&r1=904154&r2=904155&view=diff
==============================================================================
--- db/derby/docs/trunk/src/tuning/ctuntransform25868.dita (original)
+++ db/derby/docs/trunk/src/tuning/ctuntransform25868.dita Thu Jan 28 16:54:18 2010
@@ -51,6 +51,8 @@
 <li>The subquery is not in the SELECT list of the outer query block. </li>
 <li>There are no aggregates in the SELECT list of the subquery. </li>
 <li>The subquery does not have a GROUP BY clause. </li>
+<li>The subquery does not have an ORDER BY, result offset, or fetch first
+clause.</li>
 <li>The subquery has a single entry in its FROM list that is a <xref href="ctuntransform13966.dita#ctuntransform13966/rtuntransform41494">base
table</xref>. </li>
 <li>None of the predicates in the subquery, including the additional one formed
 between the left side of the subquery operator and the column in the subquery's

Modified: db/derby/docs/trunk/src/tuning/ctuntransform36368.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tuning/ctuntransform36368.dita?rev=904155&r1=904154&r2=904155&view=diff
==============================================================================
--- db/derby/docs/trunk/src/tuning/ctuntransform36368.dita (original)
+++ db/derby/docs/trunk/src/tuning/ctuntransform36368.dita Thu Jan 28 16:54:18 2010
@@ -92,6 +92,8 @@
 <li>The subquery is not in the SELECT list of the outer query block. </li>
 <li>There are no aggregates in the SELECT list of the subquery. </li>
 <li>The subquery does not have a GROUP BY clause. </li>
+<li>The subquery does not have an ORDER BY, result offset, or fetch first
+clause.</li>
 <li>There is a uniqueness condition that ensures that the subquery does not
 introduce any duplicates if it is flattened into the outer query block. </li>
 <li>Each table in the subquery's FROM list (after any view, derived table,



Mime
View raw message