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.
Table of contents
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
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.