| Select Top Rows |
In this video we’ll walk you though how to select top rows within a query. We’ll show you how to retrieve the top or bottom rows in the result. We’ll also show you how to use one sort to select the rows, and once completed, another sort to present them (tricky). For instance you may want the bottom performing sales people according to sales year to date, sorted by last name.
Table of contents
Once you have watched the video check out the sample code below. I’ve also included a transcript for you to use.
Transcript to Select Top Rows
Welcome to another episode of Essential SQL Minute. Today, let’s learn how to retrieve the top or bottom of our result set. Here on the screen you can see that I have a result set or a query that’s going to pull back every salesperson from the vSalesPerson table.
All Rows
SELECT FirstName, LastName, SalesLastYear FROM Sales.vSalesPerson ORDER BY SalesLastYear Desc
When I execute it, it brings back 17 rows. But let’s suppose I want to see the highest performing sales people. In fact, I want to see those top 10 sales people by sales for last year.
Well to do that, what I can do is use what’s called the TOP clause. In here I will show you the queries. Let’s select top 10 and then the columns, first name, last name and so on from sales person.
Select Top Rows
SELECT TOP 10 FirstName,
LastName,
SalesLastYear
FROM Sales.vSalesPerson
ORDER BY SalesLastYear Desc
Now the trick here is, is that I’m ordering the result by sales last year in descending order, that means the sales are going to go from 10, 9, 8, 7, 6, 5… as opposed to ascending order, which would 0, 1, 2, 3.
When I run this you’ll see now that we have the top performing sales person as our first result on down. There’s a total of 10.
Now conversely I could also get the poorest performing sales people and to do so I would run pretty much the same exact query, top 10. But now the trick is, is I’m going to do it in ascending order. The sales are going to go from 0 to 1 to 2 to 3. We’re going to get the top 10 in that order.
Bottom Five Rows
SELECT TOP 10 FirstName,
LastName,
SalesLastYear
FROM Sales.vSalesPerson
ORDER BY SalesLastYear ASC
I execute that, you’ll see that we get some sales people that had no sales last year and then the sales slowly go up.
Now I could also get the same people sorted by their last name. To do that it’s slightly trickier. I need to use what’s called a common table expression. Let me introduce that.
Here you’ll see that we have the same query (the query in green), the select top 10 in ascending order but this is wrapped into what’s called a common table expression, which is a way of providing a result within a query.
Order Bottom Ten in List
WITH CTE_SalesPerson (FirstName, LastName, SalesLastYear) AS ( SELECT TOP 10 FirstName, LastName, SalesLastYear FROM Sales.vSalesPerson ORDER BY SalesLastYear Asc ) SELECT FirstName, LastName, SalesLastYear FROM CTE_SalesPerson ORDER BY LastName
This common table expression CTE_SalesPerson (the text in blue), it’s going to return the first name, last name and sales last year from this query.
In fact the only rows in CT sales person are going to be those 10 rows, these top 10 rows, which just happen to be the lowest performing sales people.
But when I do select from this table (the text in red), the cool thing is now is I can order by LastName. Since I’m only working with those 10, when I run this query, what you’re going to see is, let me get the result here.
That is now bringing in those lowest performing sales people, but it’s now ordered by their last name.
