[Db-derby Wiki] Update of "OLAPRowNumber" by BryanPendleton

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by BryanPendleton:
http://wiki.apache.org/db-derby/OLAPRowNumber

New page:
As part of the ["OLAPOperations"] effort, we'd like to add support for the ROW_NUMBER function.

== Rationale ==

The SQL 2003 standard specifies the ROW_NUMBER function. This function computes the sequential row number, starting with 1, of the row within its window partition according to the ordering of the window.

== Syntax ==

In the SQL 2003 standard, the {{{<window function>}}} has the following (partial) grammar:

{{{
<window function> ::= <window function type> OVER <window name or specification>

<window function type> ::=
    <rank function type> <left paren> <right paren>
  | ROW_NUMBER <left paren> <right paren>
  | <aggregate function>
}}}

Thus the ROW_NUMBER function has a very simple syntax.

== Example ==

A nice example of the ROW_NUMBER function is provided in DERBY-581:

{{{
SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n
}}}

In this example the ROW_NUMBER function is used to limit the query as soon as the first N rows have been determined.