Sorting results from SKIP, LIMIT, or FIRST queries

When you combine the ORDER BY clause with the SKIP, LIMIT, or FIRST options in a query, the ORDER BY clause can affect which qualifying rows are in the result set.

Queries that combine the SKIP and the FIRST or LIMIT options with the ORDER BY clause impose a unique order on the qualifying rows. Successive queries that increment the offset value by the value of max can partition the results into distinct subsets of max rows. This capability can support web applications that require a fixed page size, without requiring cursor management.

Using FIRST or LIMIT with ORDER BY

For a query that includes the FIRST or LIMIT optionor the LIMIT clause and an ORDER BY clause, the qualifying rows that are returned in their sort-key order might not be the same result set that the query without ORDER BY returned in the order of retrieval.

With an ORDER BY clause, you can retrieve the first max qualifying rows. For example, the following query finds the ten highest-paid employees:

SELECT FIRST 10 name, salary FROM emp ORDER BY salary DESC;

You can use the FIRST option in a query whose result set defines collection-derived table (CDT) within the FROM clause of another SELECT statement. The following query specifies a CDT that has no more than ten rows:

SELECT * 
   FROM TABLE(MULTISET(SELECT FIRST 10 * FROM employees 
   ORDER BY employee_id)) vt(x,y), tab2 
   WHERE tab2.id = vt.x;

Using SKIP and ORDER BY

For a query in which the SKIP option defines an integer offset of qualifying rows that are ignored before the first returned row, the order of retrieval determines which rows are omitted from the query result if the ORDER BY clause is absent. However, if the ORDER BY clause is included the offset is applied to the sorted rows. Whether sorted in ascending or descending order, these rows that are excluded based on their sort-key value are generally uncorrelated with the qualifying rows that are excluded based on their order of retrieval, unless the query result set is empty.

When you use the SKIP option in a query with an ORDER BY clause, you can exclude the first offset rows that have the lowest values according to the ORDER BY criteria. You can also use SKIP to exclude rows with the highest values, if the ORDER BY clause includes the DESC keyword. For example, the following query returns all rows of the orders table, except for the fifty oldest orders:

SELECT SKIP 50 * FROM orders ORDER BY order_date;

Here the result set is empty if there are fewer than 50 rows in the orders table. An offset of zero ( 0 ) is not invalid, but in that case the SKIP option does nothing.

The following example query contains a collection subquery that performs the following actions:

retrieves only the eleventh through fifteenth rows from tab3 as a collection-derived table,
sorts those five rows by the ascending order of their value in column a,
and stores this result set in a temporary table.

SELECT * FROM TABLE (MULTISET (SELECT SKIP 10 FIRST 5 a FROM tab3 
                     ORDER BY a)) INTO TEMP;

The following INSERT statement includes a collection subquery whose results define a collection-derived table. The rows are ordered by the value in column a, and are inserted into table tab1.

INSERT INTO tab1 (a) SELECT * 
   FROM TABLE (MULTISET (SELECT SKIP 10 FIRST 5 a 
                            FROM tab3 ORDER BY a));