TOP Clause in SQL

In this video, we’ll show you how to use the TOP clause to return a portion of the result. This comes in handy in situations you’re required to return the top or bottom ten entries of a list.

This video is an excerpt for our SQL 101 video class. You can learn more about SQL 101 here. I would encourage you to look into the class. It is a great way to get started learning SQL.

TOP Clause – Video Transcript

Below is the transcript of the video. It has been cleaned up a bit, to make it easier to read.

In many cases, you’ll use the order by to sort your entire result set, but then maybe only want to see the top portion of the result.

To do that, you can use what’s called the TOP clause. Here, you can see I’ve selected the first and last name from the person table, and what I’d like to do is order by last name and, let’s say, select the top five people. To do that, I can use the TOP clause. I’d say select TOP 5 FirstName and LastName from Person table.

SELECT   TOP 5 FirstName, LastName
FROM     Person.Person
ORDER BY LastName

Now, this TOP 5 will affect the entire result. I’m not just saying to select the top five first names, it’s selecting the top five results from the query, where I’m selecting first name and last name from the person table where the results are ordered by last name. When I run this, you’ll see that I just get five rows, and it was the top five records from the result.

To clarify, the order is like this:

• First run the query to get all the results…
• Now apply the TOP clause to return just a portion…

Likewise, if I wanted to look at them, I’ll call it the bottom five rows, the way to do that would be to sort the result set in descending order, and then take the top five rows from that result.

If I do that, you’ll see where … Let me first get rid of the TOP, so it’s descending. Here we have Zillig and Zimmerman as our results. If I take the top five here and execute, you’ll see that I’m getting essentially the top five folks that were at the bottom. It’s really the bottom five results of the table.

It may sound confusing, I’m using top when I’m using when I’m talking about bottom, but the idea here is I essentially sorting the result in descending order and them skimming the top off that which in English would be the bottom of the result set.

TOP PERCENT

I could also get the top 5%, so if I wanted to not just get the top five people but the top percentage, I could type in here TOP 5 PERCENT and this will get back more than five rows, it’s going to bring back 5% of the total result set.

When I execute this, you’ll see I’m now getting back 999 rows.

This result set was very large, and this is just another way of pulling back a relative number of rows.

Maybe you are doing an audit and you need to bring back the top 5% or top 10% of rows. Again, you can type in any number you wish here, so here’s top 10% rows. I think if I go above 100, it’ll error. It will, because percentages need to between zero and one. If I take, say, TOP 0 PERCENT, I get zero rows.