Return the First Rows from Your Result

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.

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 salesperson.

Select Top Rows

SELECT    TOP 10 FirstName,
          LastName,
          SalesLastYear
FROM      Sales.vSalesPerson
ORDER BY  SalesLastYear Desc

Now the trick here 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 salespeople and to do so, I would run pretty much the same exact query, top 10. But now the trick 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 salespeople 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 bold), 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.

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 italics), is going to return the first name, last name and sales last year from this query.

In fact, the only rows in CT salesperson are going to be those 10 rows, these top 10 rows, which just happen to be the lowest performing salespeople.

But when I do select from this table (the underlined text), 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.