Explicitly limiting the number of rows returned by a query

You can use the FIRST or TOP keywords to limit the number of rows included in the result set of a query. These keywords are for use with queries that include an ORDER BY clause.

Examples

The following query returns information about the employee that appears first when employees are sorted by last name:

SELECT FIRST *
   FROM Employees
   ORDER BY Surname;

The following query returns the first five employees as sorted by last name:

SELECT TOP 5 *
   FROM Employees
   ORDER BY Surname;

When you use TOP, you can also use START AT to provide an offset. The following statement lists the fifth and sixth employees sorted in descending order by last name:

SELECT TOP 2 START AT 5 *
   FROM Employees
   ORDER BY Surname DESC;

FIRST and TOP should be used only in conjunction with an ORDER BY clause to ensure consistent results. Use of FIRST or TOP without an ORDER BY triggers a syntax warning, and will likely yield unpredictable results.

Note

The START AT value must be greater than 0. The TOP value must be greater than 0 when a constant and greater or equal to 0 when a variable.