Try reloading this page, or reviewing your browser settings
This final video segment demonstrates “TOP N” queries that can be used to answer business questions based on certain parameters, such as which items sell best, or which sell least.
About this video
- John Deardurff
- First online
- 23 November 2018
- Online ISBN
- Copyright information
- © John Deardurff 2019
In this video segment, we’re going to talk about how to sort records in a SELECT statement by using the TOP keyword. So let’s get into our demonstration.
So as you can see, I’ve already opened up SQL server Management Studio, and in our query editor window I have written a SELECT statement to select the SalesOrderID and the OrderDate fields from the Sales.SalesOrderHeader table. I’ve already executed this query and we can see that we have 31,465 records or rows. That’s how many orders that AdventureWorks has taken. Now I may want to sort these in a specific order. So I can use my ORDER BY statement, let’s use an ORDER BY, ORDER BY, and let’s say Sales, SalesOrderID, we’ll type that in, and we’ll put it in descending order. So you can see currently the first order that was placed is the SalesOrderID 43,659, or 43659.
Now I when I execute this query, it’s going to put this in descending order so the last order that was ever made, apparently in June of 2014, the last order that was placed was 75123. So we can see the first one was 43659, the last order ID was 75123. Let’s go ahead an put this back, SalesOrderID back in ascending order, so I’ll execute and again, 43659.
Now let’s see if I wanted to see the top ten orders or the first ten, I should say. So in my statement here I can type in TOP TEN and it will give me my top ten records, top ten. So there’s only ten rows returned. Now if I wanted to see the last ten, there’s not a bottom keyword. I would just need to sort my records in descending order, so like I did previously but this time I’m going to do it again but it’s going to show the bottom ten, or the last ten orders that were created. So that’s using the top keyword.
Now I do need to be careful with this. If I did something like top 100, this is actually going to show me my top 100 records, or actually the last 100 records that was in this table. But what if I just wanted a percentage, just wanted a percentage of records? What I can do, I can type in TOP TEN PERCENT, and that would give me, TOP TEN PERCENT, execute, and I believe there is around 31,000 records. Now that I’ve said top ten percent, it’s actually giving me, since it’s in descending order, it’s the bottom ten percent or the last ten percent of my records. But 3,000 is about ten percent of 30,000.
If I still wanted all my records, well I could remove all those records, but I can do something like top 100 percent and it would show me just all the records again, back in descending order. It kind of gets us back to where we originally were without that top keyword.
So in this video segment, we have seen how to sort records in a SELECT statement by using the top keyword. Thank you for watching.