Top ten results in a query

It's easy to set a query into the right order but sometimes you only want to see the first few records - the "top ten". The standard Query Designer doesn't let you do this but by switching to the SQL view you can easily get the result you want. The example below gets the ten most expensive items from the Products table of the Northwind database.

Start by creating a query from the products table and adding the ProductName and UnitPrice fields. Set the UnitPrice field into descending order so that the most expensive items are at the top of the list.

This query will show the items that you want to see but it will also show the rest of the items. The next step is to hide these cheaper items.

Switch to SQL view by right-clicking on the Designer and selecting SQL View from the menu. This will show that the query is based on three lines of SQL:

Change the first line by adding the words TOP TEN to the first line of the SQL:

Switch back to the Datasheet View and you'll see the most expensive ten products from the table. You can also add the key word PERCENT and get the query to show the most expensive 10% of the table.