Edit

TOP (Entity SQL)

  • Article

The SELECT clause can have an optional TOP sub-clause following the optional ALL/DISTINCT modifier. The TOP sub-clause specifies that only the first set of rows will be returned from the query result.

Syntax

[ TOP (n) ]

Arguments

n The numeric expression that specifies the number of rows to be returned. n could be a single numeric literal or a single parameter.

Remarks

The TOP expression must be either a single numeric literal or a single parameter. If a constant literal is used, the literal type must be implicitly promotable to Edm.Int64 (byte, int16, int32 or int64 or any provider type that maps to a type that is promotable to Edm.Int64) and its value must be greater than or equal to zero. Otherwise an exception will be raised. If a parameter is used as an expression, the parameter type must also be implicitly promotable to Edm.Int64, but there will be no validation of the actual parameter value during compilation because the parameter values are late bounded.

The following is an example of constant TOP expression:

select distinct top(10) c.a1, c.a2 from T as a

The following is an example of parameterized TOP expression:

select distinct top(@topParam) c.a1, c.a2 from T as a

TOP is non-deterministic unless the query is sorted. If you require a deterministic result, use the SKIP and LIMIT sub-clauses in the ORDER BY clause. The TOP and SKIP/LIMIT are mutually exclusive.

Example

The following Entity SQL query uses the TOP to specify the top one row to be returned from the query result. The query is based on the AdventureWorks Sales Model. To compile and run this query, follow these steps:

  1. Follow the procedure in How to: Execute a Query that Returns StructuralType Results.

  2. Pass the following query as an argument to the ExecuteStructuralTypeQuery method:

    SELECT VALUE TOP(1) contact FROM AdventureWorksEntities.Contacts AS contact

See also