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

[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 JohnHEmbretsen:
http://wiki.apache.org/db-derby/OLAPRowNumber

The comment on the change is:
Added examples and doc references

------------------------------------------------------------------------------
  
  Thus the ROW_NUMBER function has a very simple syntax.
  
- == Example ==
+ == Examples ==
  
  A nice example of the ROW_NUMBER function is provided in DERBY-581:
  
@@ -37, +37 @@

  
  In this example the ROW_NUMBER function is used to limit the query as soon as the first N rows have been determined.
  
+ Note that the above example is not supported by the current implementation (Derby 10.4.1.3 / May 2008), since this implementation has some [http://db.apache.org/derby/docs/10.4/ref/rreffuncrownumber.html limitations].
+ 
+ Examples supported by the current implementation are included below, as well as on the [http://db.apache.org/derby/faq.html#limit Derby FAQ web page] since the ROW_NUMBER function may be used as a replacement for the `LIMIT` and `OFFSET` keywords used in some other databases (and many users ask about this functionality).
+ 
+ For example, querying a table with 500000 rows, retrieving the first 5 rows only (using the IJ tool):
+ 
+ {{{
+ ij> SELECT * FROM (
+ >     SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*
+ >     FROM myLargeTable
+ >   ) AS tmp
+ >   WHERE rownum <= 5;
+ 
+ 
+ ROWNUM              |A          |B                                                 
+ -----------------------------------------------------------------------------------
+ 1                   |1          |This is row number 1                              
+ 2                   |2          |This is row number 2                              
+ 3                   |3          |This is row number 3                              
+ 4                   |4          |This is row number 4                              
+ 5                   |5          |This is row number 5                              
+ 
+ 5 rows selected
+ }}}
+ (corresponding to "`SELECT * FROM myLargeTable LIMIT 5`" in some other databases)
+ 
+ Example retreiving rows 200001 to 200005:
+ 
+ {{{
+ ij> SELECT * FROM (
+ >     SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*
+ >     FROM myLargeTable
+ >   ) AS tmp
+ >   WHERE rownum > 200000 AND rownum <= 200005;
+ 
+ 
+ ROWNUM              |A          |B                                                 
+ -----------------------------------------------------------------------------------
+ 200001              |200001     |This is row number 200001                         
+ 200002              |200002     |This is row number 200002                         
+ 200003              |200003     |This is row number 200003                         
+ 200004              |200004     |This is row number 200004                         
+ 200005              |200005     |This is row number 200005                         
+ 
+ 5 rows selected
+ }}}
+ (corresponding to "`SELECT * FROM myLargeTable LIMIT 5 OFFSET 200000`" in some other databases)
+ 
  == Current Implementation ==
  
- The ROW_NUMBER() window function is currently not implemented, and thereby not supported.
+ An implementation of the ROW_NUMBER() window function is included in Derby starting with the 10.4.1.3 release. Limitations and usage description may be found in the Derby Reference Manual, [http://db.apache.org/derby/docs/dev/ref/rreffuncrownumber.html ROW_NUMBER built-in function].
+ 
  
  == Proposed Changes ==