svn commit: r1478024 - in /db/derby/docs/branches/10.10/src/ref: refderby.ditamap rreffuncrownumber.dita rrefkeywords29722.dita rrefselectexpression.dita rrefsqljwindow.dita

[ch...@apache.org]

Author: chaase3
Date: Wed May  1 14:31:42 2013
New Revision: 1478024

URL: http://svn.apache.org/r1478024
Log:
DERBY-6103  Improve documentation of ROW_NUMBER function

Merged patch DERBY-6103-5.diff to 10.10 doc branch from trunk revision 1477997.

Added:
    db/derby/docs/branches/10.10/src/ref/rrefsqljwindow.dita
      - copied unchanged from r1477997, db/derby/docs/trunk/src/ref/rrefsqljwindow.dita
Modified:
    db/derby/docs/branches/10.10/src/ref/refderby.ditamap
    db/derby/docs/branches/10.10/src/ref/rreffuncrownumber.dita
    db/derby/docs/branches/10.10/src/ref/rrefkeywords29722.dita
    db/derby/docs/branches/10.10/src/ref/rrefselectexpression.dita

Modified: db/derby/docs/branches/10.10/src/ref/refderby.ditamap
URL: http://svn.apache.org/viewvc/db/derby/docs/branches/10.10/src/ref/refderby.ditamap?rev=1478024&r1=1478023&r2=1478024&view=diff
==============================================================================
--- db/derby/docs/branches/10.10/src/ref/refderby.ditamap (original)
+++ db/derby/docs/branches/10.10/src/ref/refderby.ditamap Wed May  1 14:31:42 2013
@@ -694,6 +694,7 @@ limitations under the License.
 <topicref href="rrefsqlj21583.dita" navtitle="FROM clause"></topicref>
 <topicref href="rrefsqlj32654.dita" navtitle="GROUP BY clause"></topicref>
 <topicref href="rrefsqlj14854.dita" navtitle="HAVING clause"></topicref>
+<topicref href="rrefsqljwindow.dita" navtitle="WINDOW clause"></topicref>
 <topicref href="rrefsqlj13658.dita" navtitle="ORDER BY clause"></topicref>
 <topicref href="rrefsqljoffsetfetch.dita" navtitle="The result offset and fetch first clauses"></topicref>
 <topicref href="rrefsqljusing.dita" navtitle="USING clause"/>

Modified: db/derby/docs/branches/10.10/src/ref/rreffuncrownumber.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/branches/10.10/src/ref/rreffuncrownumber.dita?rev=1478024&r1=1478023&r2=1478024&view=diff
==============================================================================
--- db/derby/docs/branches/10.10/src/ref/rreffuncrownumber.dita (original)
+++ db/derby/docs/branches/10.10/src/ref/rreffuncrownumber.dita Wed May  1 14:31:42 2013
@@ -26,18 +26,24 @@ limitations under the License.
 </keywords>
 </metadata></prolog>
 <refbody>
-<section> <p>The ROW_NUMBER function does not take any arguments, and for each row over the window it returns an ever increasing BIGINT. It is normally used to limit the number of rows returned for a query. The LIMIT keyword used in other databases is not defined in the SQL standard, and is not supported.</p>
-<ul>
-<li>Derby does not currently allow the named or unnamed window specification to be specified in the <codeph>OVER()</codeph> clause, but requires an empty parenthesis. This means the function is evaluated over the entire result set.</li>
-<li>The ROW_NUMBER function cannot currently be used in a WHERE clause.</li>
-<li>Derby does not currently support ORDER BY in subqueries, so there is currently no way to guarantee the order of rows in the SELECT subquery. An optimizer override can be used to force the optimizer to use an index ordered on the desired column(s) if ordering is a firm requirement.</li>
-</ul>
-<p>The data type of the returned value is a BIGINT number.</p> 
+<section> <p>The ROW_NUMBER function does not take any arguments, and for each
+row over the window it returns an ever increasing BIGINT. It is normally used to
+limit the number of rows returned for a query. A
+<xref href="rrefsqljoffsetfetch.dita#rrefsqljoffsetfetch">result offset or fetch
+first clause</xref> can be a more efficient way to perform this task.</p>
+<p>The data type of the returned value is BIGINT.</p> 
 </section>
-
-<refsyn><title>Syntax</title> <codeblock><b>ROW_NUMBER() OVER ()</b></codeblock> </refsyn>
-
-<example><title>Example</title><p>To limit the number of rows returned from a query to the 10 first rows of table <codeph>T</codeph>, use the following query:</p>
+<refsyn><title>Syntax</title> 
+<codeblock><b>ROW_NUMBER() OVER [ <i>WindowSpecification</i> | <i>WindowName</i> ]</b></codeblock> 
+<p>Currently, the only valid <i>WindowSpecification</i> is an empty pair of
+parentheses (<codeph>()</codeph>), which indicates that the function is
+evaluated over the entire result set.</p>
+<p>If you choose to use a
+<xref href="rrefsqljwindow.dita#rrefsqljwindow">WINDOW clause</xref> in a
+<i><xref href="rrefselectexpression.dita#rrefselectexpression">SelectExpression</xref></i>
+to specify a window, you must specify a <i>WindowName</i> to refer to it.</p>
+</refsyn>
+<example><title>Examples</title><p>To limit the number of rows returned from a query to the 10 first rows of table <codeph>T</codeph>, use the following query:</p>
 <codeblock>
 SELECT * FROM (
    SELECT 
@@ -47,6 +53,14 @@ SELECT * FROM (
 ) AS TR 
    WHERE R &lt;= 10; 
 </codeblock>
+<p>To display the result of a query using a window name in a WINDOW clause:
+</p>
+<codeblock>
+SELECT ROW_NUMBER() OVER R, 
+    B, 
+    SUM(A) 
+FROM T5 GROUP BY B WINDOW R AS ()
+</codeblock>
 </example>
 </refbody>
 </reference>

Modified: db/derby/docs/branches/10.10/src/ref/rrefkeywords29722.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/branches/10.10/src/ref/rrefkeywords29722.dita?rev=1478024&r1=1478023&r2=1478024&view=diff
==============================================================================
--- db/derby/docs/branches/10.10/src/ref/rrefkeywords29722.dita (original)
+++ db/derby/docs/branches/10.10/src/ref/rrefkeywords29722.dita Wed May  1 14:31:42 2013
@@ -234,6 +234,7 @@ compact="yes">
 <sli>VIEW</sli>
 <sli>WHENEVER</sli>
 <sli>WHERE</sli>
+<sli>WINDOW</sli>
 <sli>WITH</sli>
 <sli>WORK</sli>
 <sli>WRITE</sli>

Modified: db/derby/docs/branches/10.10/src/ref/rrefselectexpression.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/branches/10.10/src/ref/rrefselectexpression.dita?rev=1478024&r1=1478023&r2=1478024&view=diff
==============================================================================
--- db/derby/docs/branches/10.10/src/ref/rrefselectexpression.dita (original)
+++ db/derby/docs/branches/10.10/src/ref/rrefselectexpression.dita Wed May  1 14:31:42 2013
@@ -33,6 +33,7 @@ other tables.</p></section>
 [ <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> ]
+[ <i><xref href="rrefsqljwindow.dita#rrefsqljwindow">WINDOW 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> ]
@@ -83,6 +84,10 @@ table. If the HAVING clause evaluates to
 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>The WINDOW clause allows you to refer to a window by name when you use a
+<xref href="rreffuncrownumber.dita#rreffuncrownumber"></xref> in a
+<i>SelectExpression</i>.
+</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
@@ -97,6 +102,7 @@ a <i>SelectExpression</i> in the followi
 <li>WHERE clause</li>
 <li>GROUP BY (or implicit GROUP BY)</li>
 <li>HAVING clause</li>
+<li>WINDOW clause</li>
 <li>ORDER BY clause</li>
 <li>Result offset clause</li>
 <li>Fetch first clause</li>