Limits the rows returned by an OpenEdge SQL query at the statement level and is supported in subqueries.

Syntax

TOP n[ column_name [, column_name ] .....| * ]

Parameters

n

Indicates the number of records per table.

[ column_name [, column_name ] .....| * ]

Indicates the columns within a table.

When the TOP clause is specified, the OpenEdge SQL server returns the maximum number of rows specified in the clause. The maximum number allowed for the TOP clause is 2,147,483,647.

Example

In the following example, the SELECT statement returns the names of the five customers with the highest account balance:

SELECT TOP 5 * FROM pub.customer 
     ORDER BY balance DESC;

The TOP clause is only allowed in a top-level SELECT statement. Therefore, the TOP clause cannot be used in the following instances:

  • When derived tables are used in the query
  • Within the CREATE TABLE, CREATE VIEW, UPDATE, and INSERT statements
  • In queries used with set operators such as UNION, INTERSECT, and MINUS

In instances when the server performs aggregation on the result set (i.e., through an aggregate function such as SUM or MAX, a GROUP BY clause, or the DISTINCT keyword) the TOP clause should be interpreted as being applied last. When there is no aggregation in the SELECT statement and the result set is also sorted, then SQL will optimize sorting in order to increase query performance.

SELECT TOP is the functional equivalent of the Oracle ROWNUM functionality. Note that SELECT TOP is defined simply in terms of a limit on the result set size, and the optimizer determines how to use this limit for best data access. Thus, SELECT TOP does not have all the "procedural rules" used to define the meaning of the Oracle ROWNUM phrase.