Using the FIRST Option

The FIRST max option specifies that the result set includes no more than max rows (or exactly max, if max is not greater than the number of qualifying rows). Any additional rows that satisfy the selection criteria are not returned. The following example retrieves at most 10 rows from table tab1:

SELECT FIRST 10 a, b FROM tab1;

Dynamic Server can use a host variable or the value of an SPL input parameter in a local variable to specify max.

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

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

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

3This example applies the FIRST option to the result of a 3UNION expression:

SELECT FIRST 10 a, b FROM tab1 UNION SELECT a, b FROM tab2 

The FIRST option is not valid in any of the following contexts:

• In the definition of a view
• In nested SELECT statements
• In subqueries
• In a singleton SELECT (where max = 1) within an SPL routine
• Where embedded SELECT statements are used as expressions