Use top n with select...into statements to limit the number of rows inserted in the target table. This is different from set rowcount, which is ignored during a select...into.
top n functionality works with Microsoft SQL Server, Adaptive Server Anywhere, and Adaptive Server IQ.
Use the top n clause in a select command to limit the number of rows in the result set to the number of rows specified by “n,” which is an unsigned 32-bit value between 0 through 232-1 (4GB-1 or 4,294,967,295). Zero indicates “no” rows.
Adaptive Server Enterprise version 12.5.3 supports the top n clause in outer query select statements, but not in the select list of a subquery. This differs from Microsoft SQL Server. Any attempt to use the top n clause with Adaptive Server in a subquery yields a syntax error.
This is the syntax for the top n clause in a select statement:
select all_distinct_clause top unsigned_integer select_list into_clause from_where_clause group_by_clause having_clause
This example selects the first 5 rows from table t1:
select top 5 col1 from t1
The syntax to use the top n clause in a select statement with a union is:
select top 2 column_name from table_name union all select top 3 column_name from table_name
This returns five rows, assuming t1 has at least 2 rows and t2 has at least 3 rows. The top limit applies to the individual selects that form a union, not to the union as a whole.
In an update statement, insert the top n clause immediately after the keyword:
UPDATE TOP unsigned_integer object_identifier SET set_clause_list from_where_clause for_clause abstract_plan_clause
In a delete statement, insert the top n clause immediately after the keyword:
DELETE TOP unsigned_integer result_table from_where_clause for_clause abstract_plan_clause
When you use top n with delete, update, or in a view, you cannot specify ordering. If there is an implied order on the table from a clustered index, that order applies, otherwise, the results are unpredictable and they can be in any order.
When used with cursors, top n limits the overall size of the result set. If you specify set cursor rowcount, top n limits the results of a single fetch.
When a view definition contains select top n and a query with a where clause uses it, the results may be inconsistent.
