Syntax

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.

Derby does not currently allow the named or unnamed window specification to be specified in the OVER() clause, but requires an empty parenthesis. This means the function is evaluated over the entire result set.
The ROW_NUMBER function cannot currently be used in a WHERE clause.
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.

The data type of the returned value is a BIGINT number.

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 +result offset or fetch +first clause can be a more efficient way to perform this task.

The data type of the returned value is BIGINT.

- -Syntax ROW_NUMBER() OVER () - -Example

To limit the number of rows returned from a query to the 10 first rows of table T, use the following query:

+Syntax +ROW_NUMBER() OVER [ WindowSpecification | WindowName ] +

Currently, the only valid WindowSpecification is an empty pair of +parentheses (()), which indicates that the function is +evaluated over the entire result set.

If you choose to use a +WINDOW clause in a +SelectExpression +to specify a window, you must specify a WindowName to refer to it.

+ +Examples

To limit the number of rows returned from a query to the 10 first rows of table T, use the following query:

SELECT * FROM ( SELECT @@ -47,6 +53,14 @@ SELECT * FROM ( ) AS TR WHERE R <= 10; +

To display the result of a query using a window name in a WINDOW clause: +

+ +SELECT ROW_NUMBER() OVER R, + B, + SUM(A) +FROM T5 GROUP BY B WINDOW R AS () + 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"> VIEW WHENEVER WHERE +WINDOW WITH WORK WRITE 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.

The WINDOW clause allows you to refer to a window by name when you use a + in a +SelectExpression. +

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 SelectExpression in the followi