Using a FIRST Clause to Select Specific Rows

You can include a FIRST clause in a SELECT statement to specify that the query returns only a specified number of the first rows that match the conditions of the SELECT statement. You include a number immediately following the FIRST keyword to specify the maximum number of rows that the query can return. The rows that the database server returns when you execute a SELECT statement with a FIRST clause might differ, depending on whether the statement also includes an ORDER BY clause.

You cannot use a FIRST clause when the SELECT statement is a subquery or part of a view definition.

For information about restrictions on use of the FIRST clause, see the description of the SELECT statement in the IBM Informix: Guide to SQL Syntax.

FIRST Clause Without an ORDER BY Clause

If you do not include an ORDER BY clause in a SELECT statement with a FIRST clause, any rows that match the conditions of the SELECT statement might be returned. In other words, the database server determines which of the qualifying rows to return, and the query result can vary depending on the query plan that the optimizer chooses.

Figure 78 uses the FIRST clause to return the first five rows from the state table.

Figure 78. Query

SELECT FIRST 5 * FROM state

Figure 79. Query Result

code sname

AK   Alaska
HI   Hawaii
CA   California
OR   Oregon
WA   Washington

You can use a FIRST clause when you simply want to know the names of all the columns and the type of data that a table contains, or to test a query that otherwise would return many rows. Figure 80 shows how to use the FIRST clause to return column values for the first row of a table.

Figure 80. Query

SELECT FIRST 1 * FROM orders

Figure 81. Query Result

order_num      1001
order_date     05/20/1998
customer_num   104
ship_instruct  express
backlog        n
po_num         B77836
ship_date      06/01/1998
ship_weight    20.40
ship_charge    $10.00
paid_date      07/22/1998

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. Figure 82 shows a query that includes an ORDER BY clause to return (by alphabetical order) the first five states contained in the state table. Figure 82, which is the same as Figure 78 except for the ORDER BY clause, returns a different set of rows than Figure 78.

Figure 82. Query

SELECT FIRST 5 * FROM state ORDER BY sname

Figure 83. Query Result

code sname

AL   Alabama
AK   Alaska
AZ   Arizona
AR   Arkansas
CA   California

Figure 84 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 84. Query

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

Figure 85. 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