top n functionality implements compatibility with MicroSoft SQL server, Adaptive Server Anywhere, and ASIQ.
Use the top n clause to limit the number of rows in the result set to the number of rows specified by the integer. The integer can be any unsigned 32-bit value, in the range 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 the MicroSoft SQL server. Any attempt to use the top n clause in a subquery yields a syntax error.
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.
This is the syntax for the top n clause a select statement:
SELECT all_distinct_clause TOP unsigned_integer select_list into_clause from_where_clause group_by_clause having_clause
Example:
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 c1 from t1 union all select top 3 c2 from t2
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 used 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 as 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.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
