Click here to Skip to main content
15,867,835 members
Articles / Programming Languages / SQL

Return the First Rows from Your Result

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
29 Dec 2019MIT3 min read 2.1K   2  
How to select top rows within a query
This post walks you through selecting top rows within a query, retrieving top or bottom rows in the result and using one sort to select the rows, and another sort to present them.

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

SQL
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

SQL
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 sales people and to do so, I would run pretty much the same exact query, top 10. But now the trick is that 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

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

SQL
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 underline), 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 italics), the cool thing is now 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.

This article was originally posted at https://www.essentialsql.com/select-top-rows-sql-server

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
-- There are no messages in this forum --