TOP N and TOP N PERCENT Predicates

Although you can use the WHERE and HAVING clauses to filter the selection of records, sometimes this isn’t sufficient. For example, you may want to select all records where the state is CA, but only see the orders for the top 10 customers. Microsoft Jet provides TOP N and TOP N PERCENT predicates to limit the presentation of records after they’re selected.

TOP N Predicate

You can use the TOP N predicate to specify that your query return only a specific number of records to your program:

SELECT TOP 5 
	Employees.EmployeeID, 
	Employees.Salary
FROM Employees
ORDER BY Employees.Salary;

The TOP criteria is applied after the records are selected. TOP doesn’t necessarily imply “higher” or “more.” Think of it as the “first n records” of the result set. For example, the previous query actually produces a list of employees with the lowest salary, not the highest, because the records are sorted in ascending order by salary. If you want to find the employees with the highest salary, sort the records in descending order based on salary:

SELECT TOP 5 
	Employees.EmployeeID, 
	Employees.Salary
FROM Employees
ORDER BY Employees.Salary DESC;

If more than one record ties for the nth value, all of the tied values are displayed. If the fifth, sixth, and seventh highest salaries are the same, the previous query retrieves seven records, not five.

Note that the selection of records is based on the entire output set, even if the query uses the GROUP BY clause to group like values. It’s not possible, for example, to retrieve the “top 5 records within each group,” or the “top 10 salesmen within each region.”

TOP N PERCENT Predicate

TOP N PERCENT works much the same as the TOP N predicate, except the number of records returned isn’t fixed. A selected percentage of the records are retrieved:

SELECT TOP 10 PERCENT 
	Products.UnitsInStock, 
	Products.ProductID, 
	Products.ProductName
FROM Products
ORDER BY Products.UnitsInStock DESC, Products.ProductName DESC;

This query retrieves the 10 percent of products that have the highest value for the UnitsInStock field.

Note If you’re working with a back-end server such as SQL Server, be cautious about using the TOP N PERCENT predicate. Because most servers don’t natively support this predicate, the output of this query must be evaluated on the local machine. It’s possible that a large number of records will need to be returned from the server in order for Microsoft Jet to determine which records to select, which could cause performance to decrease.