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.

The following query uses the FIRST clause to return the first five rows from the state table.

Figure 1: Query

SELECT FIRST 5 * FROM state;

Figure 2: 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. The following query shows how to use the FIRST clause to return column values for the first row of a table.

Figure 3: Query

SELECT FIRST 1 * FROM orders;

Figure 4: 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