Finding the Top Records in a Query

You can use the TOP keyword in a SQL query to return a specified number of items from a table.  You can use the ORDER BY clause to affect which items you will return.

The TOP Keyword

You can add the TOP keyword followed by a number immediately after the SELECT keyword in a query:

You can use any whole number after the word TOP to return that number of items.

Using Sorting to Change the Top Records

To make the TOP keyword more useful you can apply sorting to the results to change which records appear at the top of the list.  The example below sorts the films in descending order of running time and then selects the top five films from the results:

This query shows the top five longest films.

To show the bottom records from the results, simply change the order of the sorting:

Here we've changed the order of the records so that the shortest films appear at the top of the list.

Including Tied Records

When you use the TOP keyword your query will return the number of records you have asked for, even if there are more records with a matching value in the sorted column.  You can display these extra records by adding the WITH TIES keywords to your statement:

We only asked to see the TOP 5 records but because there was one more film with the same running time as the fifth we see that this is included in the results.

Showing the Top Records as a Percentage

Rather than asking to see a specific number of records you can ask to see a percentage of the total records returned by a query.  To do this you just need to add the PERCENT keyword to your statement:

The table of films contains 260 records in total. When we ask for the TOP 10 PERCENT we return to top 26 records based on the sort order of the results.