Derby Limit Query - How to Limit Rows Returned in Query Results

Sometimes it is useful to limit the number of rows that are returned from a query. This can be especially useful when querying very large tables.

The Apache Derby database uses the fetch first n rows syntax to limit rows returned from a query. Substiture a numeric value for "n" when writing the query. Listed below are examples of limiting rows with the Derby database:

Example 1: Returning the first 10 rows from a table called employee: select * from employee fetch first 10 rows only; Example 2: Returning the first 10000 rows from a table called employee and only selecting a subset of columns: select fname, lname from employee fetch first 10000 rows only; Derby also supports a ROW_NUMBER() function as of version 10.4. The ROW_NUMBER() function allows the user to query for sections of a table. Listed below is an example: SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, employee.* FROM employee ) as TMP WHERE rownum > 1 AND rownum <= 5;

Many other databases also support limiting rows returned from queries. Listed below are links that show how to limit rows for other popular databases: