db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From chaa...@apache.org
Subject svn commit: r1477997 - in /db/derby/docs/trunk/src/ref: refderby.ditamap rreffuncrownumber.dita rrefkeywords29722.dita rrefselectexpression.dita rrefsqljwindow.dita
Date Wed, 01 May 2013 13:38:05 GMT
Author: chaase3
Date: Wed May  1 13:38:05 2013
New Revision: 1477997

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

Modified 3 Reference Manual topics and added a new one.

Patch: DERBY-6103-5.diff

Added:
    db/derby/docs/trunk/src/ref/rrefsqljwindow.dita   (with props)
Modified:
    db/derby/docs/trunk/src/ref/refderby.ditamap
    db/derby/docs/trunk/src/ref/rreffuncrownumber.dita
    db/derby/docs/trunk/src/ref/rrefkeywords29722.dita
    db/derby/docs/trunk/src/ref/rrefselectexpression.dita

Modified: db/derby/docs/trunk/src/ref/refderby.ditamap
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/refderby.ditamap?rev=1477997&r1=1477996&r2=1477997&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/refderby.ditamap (original)
+++ db/derby/docs/trunk/src/ref/refderby.ditamap Wed May  1 13:38:05 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/trunk/src/ref/rreffuncrownumber.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rreffuncrownumber.dita?rev=1477997&r1=1477996&r2=1477997&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rreffuncrownumber.dita (original)
+++ db/derby/docs/trunk/src/ref/rreffuncrownumber.dita Wed May  1 13:38:05 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/trunk/src/ref/rrefkeywords29722.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefkeywords29722.dita?rev=1477997&r1=1477996&r2=1477997&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefkeywords29722.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefkeywords29722.dita Wed May  1 13:38:05 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/trunk/src/ref/rrefselectexpression.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefselectexpression.dita?rev=1477997&r1=1477996&r2=1477997&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefselectexpression.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefselectexpression.dita Wed May  1 13:38:05 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>

Added: db/derby/docs/trunk/src/ref/rrefsqljwindow.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqljwindow.dita?rev=1477997&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqljwindow.dita (added)
+++ db/derby/docs/trunk/src/ref/rrefsqljwindow.dita Wed May  1 13:38:05 2013
@@ -0,0 +1,51 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!--
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements.  See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License.  You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+-->
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<reference id="rrefsqljwindow" xml:lang="en-us">
+<title>WINDOW clause</title>
+<prolog><metadata><keywords>
+<indexterm>WINDOW clause</indexterm>
+</keywords></metadata></prolog>
+<refbody>
+<section>
+<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><xref href="rrefselectexpression.dita#rrefselectexpression">SelectExpression</xref></i>.
+</p>
+</section>
+<refsyn>
+<title>Syntax</title>
+<codeblock><b>WINDOW <i>WindowName</i> AS <i>WindowSpecification</i></b></codeblock>
+<p>In a WINDOW clause, <i>WindowName</i> is a
+<xref href="crefsqlj34834.dita#crefsqlj34834">SQL92Identifier</xref>.</p>
+<p>Currently, the only valid <i>WindowSpecification</i> is a set of empty
+parentheses (<codeph>()</codeph>), which indicates that the function is
+evaluated over the entire result set.</p>
+</refsyn>
+<example><title>Example</title>
+<codeblock>
+SELECT ROW_NUMBER() OVER R, 
+    B, 
+    SUM(A) 
+FROM T5 GROUP BY B WINDOW R AS ()
+</codeblock>
+</example>
+</refbody>
+</reference>
+

Propchange: db/derby/docs/trunk/src/ref/rrefsqljwindow.dita
------------------------------------------------------------------------------
    svn:eol-style = native



Mime
View raw message