db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "John H. Embretsen (JIRA)" <j...@apache.org>
Subject [jira] Issue Comment Edited: (DERBY-232) Open Invitation to add a Question and Answer to Derby FAQs
Date Tue, 06 May 2008 15:00:56 GMT

    [ https://issues.apache.org/jira/browse/DERBY-232?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12594597#action_12594597
] 

johnemb edited comment on DERBY-232 at 5/6/08 7:59 AM:
-----------------------------------------------------------------

I'd like to update the FAQ answer for the question "Does Derby support a LIMIT command?" to
include information about the new ROW_NUMBER function. Forrest/Website diff is attached (limit-rownumber.diff).

My proposed new text is included below. Let me know if there are any issues with it. I plan
to commit and do the update within a day or two.

The patch includes links to the following web pages:
 1:  http://mail-archives.apache.org/mod_mbox/db-derby-dev/200411.mbox/%3c41A95632.3010301@debrunners.com%3e
 2: http://www.onjava.com/pub/a/onjava/2007/01/31/tuning-derby.html?page=1
 3: http://wiki.apache.org/db-derby/OLAPRowNumber
 4: manuals/index.html

I had to use some less than elegant workarounds in HTML to be able to present examples with
the desired formatting on multiple lines with Forrest, so let me know if you have better solutions
for this.

--- --- ---

Derby supports limiting the number of rows returned by a query through 
JDBC [1]. For example, to fetch the first 5 rows of a large table:

Statement stmt = con.createStatement();
stmt.setMaxRows(5);
stmt.execute("SELECT * FROM myLargeTable");

Some related tuning tips are available in this external article [2].

Starting with the 10.4.1.3 release Derby also supports limiting the 
number of rows using the ROW_NUMBER function.

For example, to fetch the first 5 rows of a large table:

SELECT * FROM (
    SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*
    FROM myLargeTable
) AS tmp
WHERE rownum <= 5;

The ROW_NUMBER function can also be used to select a limited number 
of rows starting with an offset, for example:

SELECT * FROM (
    SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*
    FROM myLargeTable
) AS tmp
WHERE rownum > 200000 AND rownum <= 200005;

For more information, refer to the ROW_NUMBER built-in function in 
the Derby Reference Manual (available from the Documentation page [4]). 
Development notes are available on the OLAPRowNumber wiki page [3].

The LIMIT keyword is not defined in the SQL standard, and is currently 
not supported.

--- --- ---


      was (Author: johnemb):
    I'd like to update the FAQ answer for the question "Does Derby support a LIMIT command?"
to include information about the new ROW_NUMBER function. Forrest/Website diff is attached
(limit-rownumber.diff).

My proposed new text is included below. Let me know if there are any issues with it. I plan
to commit and do the update within a day or two.

The patch includes links to the following web pages:
 1:  http://mail-archives.apache.org/mod_mbox/db-derby-dev/200411.mbox/%3c41A95632.3010301@debrunners.com%3e
 2: http://www.onjava.com/pub/a/onjava/2007/01/31/tuning-derby.html?page=1
 3: http://wiki.apache.org/db-derby/OLAPRowNumber
 4: manuals/index.html

I had to use some less than elegant workarounds in HTML to be able to present examples with
the desired formatting on multiple lines with Forrest, so let me know if you have better solutions
for this.

--- --- ---

Derby supports limiting the number of rows returned by a query through 
JDBC [1]. For example, to fetch the first 5 rows of a large table:

Statement stmt = con.createStatement();
stmt.setMaxRows(5);
stmt.execute("SELECT * FROM myLargeTable");

Some related tuning tips are availale in this external article [2].

Starting with the 10.4.1.3 release Derby also supports limiting the 
number of rows using the ROW_NUMBER function.

For example, to fetch the first 5 rows of a large table:

SELECT * FROM (
    SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*
    FROM myLargeTable
) AS tmp
WHERE rownum <= 5;

The ROW_NUMBER function can also be used to select a limited number 
of rows starting with an offset, for example:

SELECT * FROM (
    SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*
    FROM myLargeTable
) AS tmp
WHERE rownum > 200000 AND rownum <= 200005;

For more information, refer to the ROW_NUMBER built-in function in 
the Derby Reference Manual (available from the Documentation page [4]). 
Development notes are available on the OLAPRowNumber wiki page [3].

The LIMIT keyword is not defined in the SQL standard, and is currently 
not supported.

--- --- ---

  
> Open Invitation to add a Question and Answer to Derby FAQs
> ----------------------------------------------------------
>
>                 Key: DERBY-232
>                 URL: https://issues.apache.org/jira/browse/DERBY-232
>             Project: Derby
>          Issue Type: Task
>          Components: Web Site
>            Reporter: Jean T. Anderson
>            Assignee: Jean T. Anderson
>            Priority: Trivial
>         Attachments: limit-rownumber.diff
>
>
> Add a comment to this Jira issue with the FAQ entry (both the question and the answer)
you would like to be added to http://db.apache.org/derby/faq.html, and the assignee will add
it. Setting the "Patch Available" flag is a great way to keep a new FAQ on the radar until
committed, even if the text of the FAQ is in a comment instead of in an actual patch.
> Indicate in your comment if the FAQ belongs in the "Developing Derby" or "Using Derby"
section.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message