From the course: Querying Microsoft SQL Server 2022

Limit results with TOP - SQL Server Tutorial

From the course: Querying Microsoft SQL Server 2022

Start my 1-month free trial Buy for my team

Limit results with TOP

“

- [Instructor] Normally when you run a select query, all of the records that match your filtering criteria are returned from the database. There is a way to limit how many records are returned using the top clause. Let's go ahead and start up a new query and take a look at how this works. In the AdventureWorks database, there's a table in the sales schema called Sales Tax Rate. I'm going to write a query that just pulls out the tax rate and the name columns from that table. When I press the execute button that'll return a total of 29 rows. These are all the tax rates that we have stored in the table. The first three are for Alberta, Ontario and Quebec in Canada. Any select statement I can return just those three records by adding top three right after the keyword select. Some people like to add the number inside of parentheses, you can do that or you can leave them omitted. It doesn't really matter. The query will run just fine either way. Now, when I press the execute button it only returns the first three records from the list. But what does this represent? These three records just happen to be the first three found in the table and that's pretty arbitrary. There's no guarantee that I'd get the same information back if I were to run the same query tomorrow. The top clause really needs to be paired with an order by clause in order to return predictable and meaningful results. So let's find the highest three tax rates by adding an order by clause to the end of the query. We'll say order by tax rate descending. Now, it'll put the records in a descending order with the highest values at the top, and then the top clause will limit the results to just the first three records in that sorting order. Now we get results that actually mean something. We can see that France, the UK, and Germany have the highest sales tax rates in the database or I can remove the descending keyword from the query and re execute it. And this will return the three lowest tax rates. Those correspond to the state of Utah, Minnesota and the Canadian GST. The top clause works with a fixed number of records to return. Here, we're just using the number three, but you can put whatever number you'd like. In fact, when you right click on a table in the Object Explorer panel, and then you'll have the option to select top 1000 rows, you'll see that it's just using top 1000 here in parentheses at the top of the query in order to limit those results. So you can put whatever number you prefer in that list. Okay, let's go ahead and close this tab and return back to our tax rate query. Alternatively, you can specify a percentage of the records to return. So instead of select top three, if I say select top 50% then if I execute the query, you'll see that we get a total of 15 rows returned. This is approximately half of the original 29 that we started with. Note that you need to use the full word percent, you can't use the percent symbol. If you try and use the percent symbol here it'll cause the query to fail with an error message. Finally, I want to point out one more trick with the top clause. Notice that I have a number of tax rates that are all 7%. However, if I change the query back to select top five and press the execute button again I just get a couple of those. I only have three at 7%, and I know that there's many more. These three that are shown are just arbitrarily chosen from all of the rows with the same 7% value. We can change the behavior of the top clause by adding the with ties keywords after it. This causes the query to return more than five rows if there are multiple records that have the same value in the last position. That way you're not arbitrarily cutting off records that otherwise have qualifying data. So even though we're only asking for a five here because I put in the with ties keyword it's ending it here at 7%, but then also showing me all of the other records that are also 7%. So that's a number of ways that you can use the top clause to limit the number of rows returned by a query. You can either use a fixed number with or without ties or specify the percentage of records that you'd like returned from the full data set. And remember to use an order by clause in the query to make sure that the results are sorted in a meaningful way first before selecting the top records.

Contents