BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
If your SQL queries return lots of records you'll probably want to sort the results in some kind of order. This blog teaches you how to use the ORDER BY clause to do exactly that!
- Sorting in SQL Server Queries
- Finding the Top Records in a Query (this blog)
This article is part of our complete SQL tutorial. You might also be interested in our introduction to SQL or advanced SQL training courses.
Posted by Andrew Gould on 23 July 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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.
When you use criteria in a query the PERCENT keyword returns a percentage of the records that are returned after the criteria have been applied.
- Sorting in SQL Server Queries
- Finding the Top Records in a Query (this blog)