FIRST clause with an ORDER BY clause

You can include an ORDER BY clause in a SELECT statement with a FIRST clause to return rows that contain the highest or lowest values for a specified column. The following query shows a query that includes an ORDER BY clause to return (by alphabetical order) the first five states contained in the state table. The query, which is the same as Figure 1 except for the ORDER BY clause, returns a different set of rows than Figure 1.

Figure 1. Query

SELECT FIRST 5 * FROM state ORDER BY sname;

Figure 2. Query result

code sname

AL   Alabama
AK   Alaska
AZ   Arizona
AR   Arkansas
CA   California

The following query shows how to use a FIRST clause in a query with an ORDER BY clause to find the 10 most expensive items listed in the stock table.

Figure 3. Query

SELECT FIRST 10 description, unit_price
   FROM stock ORDER BY unit_price DESC;

Figure 4. Query result

description     unit_price

football           $960.00
volleyball         $840.00
baseball gloves    $800.00
18-spd, assmbld    $685.90
irons/wedge        $670.00
basketball         $600.00
12-spd, assmbld    $549.00
10-spd, assmbld    $499.99
football           $480.00
bicycle brakes     $480.00

Applications can use the SKIP and FIRST keywords of the Projection clause, in conjunction with the ORDER BY clause, to perform successive queries that incrementally retrieve all of the qualifying rows in subsets of some fixed size (for example, the maximum number of rows that are visible without scrolling a screen display). You can accomplish this by incrementing the offset parameter of the SKIP clause by the max parameter of the FIRST clause after each query. By imposing a unique order on the qualifying rows, the ORDER BY clause ensures that each query returns a disjunct subset of the qualifying rows.

The following query shows a query that includes SKIP, FIRST, and ORDER BY specifications to return (by alphabetical order) the sixth through tenth states in the state table, but not the first five states. This query resembles Figure 1, except that the SKIP 5 specification instructs the database server to returns a different set of rows than Figure 1.

Figure 5. Query

SELECT SKIP 5 FIRST 5 * FROM state ORDER BY sname;

Figure 6. Query result

code sname

CO   Colorado
CT   Connecticut
DE   Delaware
FL   Florida
GA   Georgia

If you use the SKIP, FIRST, or LIMIT keywords, you must take care to specify parameters that correspond to the design goals of your application. If the offset parameter of skip is larger than the number of qualifying rows, then any FIRST or LIMIT specification has no effect, and the query returns nothing.