SQL TOP

Some times you just need to use SQL TOP to reduce the number of rows shown in your result. This is handy for troubleshooting for showing a summary, such as the first in a list.

SQL TOP Video

I put together a video to help you understand TOP. If covers the main topics presented in this article. Enjoy!

SQL SELECT TOP Results

Here you can use use the SQL SELECT TOP keyword show a limited number of rows from the top of your result.

SQL TOP becomes more meaningful when used in conjunction with ORDER BY.  As then you’re able to find rows with top values. 

For example to only show the first ten people from the Person table use the following command:

Try it out! If you change TOP to 20, what would you expect to happen?

/* Answer */
SELECT   TOP 20 LastName, FirstName
FROM     Person.Person
ORDER BY LastName

You can also use SQL SELECT TOP to show the “bottom ten” results by ordering the result in descending order like so!

/* Answer */
SELECT   TOP 10 LastName, FirstName
FROM     Person.Person
ORDER BY LastName

Keep in mind that “Top” mean the first rows in the result set, not those with the greatest value. That’s why we need to sort…

So far we see how to use TOP to find the top 10 items in a list. However, you can also use TOP to find the TOP percentage as well.

SQL SELECT TOP PERCENT

To find the Top 5 percent of Employees with available vacation hours, we could write a query like so:

/* Answer */
SELECT TOP 5 PERCENT NationalIDNumber,JobTitle, VacationHours
FROM HumanResources.Employee
ORDER BY VacationHours Desc

What query would you write to get the to 10 employees with remaining vacation hours?

/* Answer */
SELECT TOP 10 NationalIDNumber,JobTitle, VacationHours
FROM HumanResources.Employee
ORDER BY VacationHours Desc

Additional SELECT TOP Resources

If you’re looking to learn more about the SQL TOP clause, then I would recommend you read our article Top Clause In SQL.

To learn more about functions, such as UPPER, check out our article Introduction to Common String Functions.

Looking for a super SQL book? Check out SQL Queries for Mere Mortals.