You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "John H. Embretsen (JIRA)" <ji...@apache.org> on 2008/05/06 17:00:56 UTC
[jira] Issue Comment Edited: (DERBY-232) Open Invitation to add a
Question and Answer to Derby FAQs
[ 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.