How do I use OFFSET and FETCH NEXT?
In this video we’ll walk you though how to use OFFSET and FETCH NEXT. We’ll show you how you can use these clauses to return a portion of your query results, such as 10 rows starting 1000 rows down.
OFFSET and FETCH NEXT are similar to TOP, but I believe they are more powerful. Once you’ve gone through this articles, I would recommend watching our next Essential SQL Minute to take it to the next level and learn how to page data.
Once you have watched the video check out the sample code below. I’ve also included a transcript for you to use.
This is an SQL Minute on OFFSET and FETCH NEXT!
Hey, this is Kris with another SQL Minute. In this episode I want to talk about OFFSET and FETCH NEXT and show you how you can use those two clauses in your order by to kind of dig into a result and pull out just a subset of the rows that are being returned.
Let’s start with a query and I’ll show you what I mean. We’re going to start out with one query here and fetch all the rows from person in order by last name.
SELECT Title, FirstName, LastName FROM Person.Person ORDER BY LastName
This return’s 19,972 rows. Now what we’re going to do is add on to the ORDER BY two additional clauses, OFFSET and FETCH NEXT, and they’re going to allow us to go down so many rows into the result and then fetch just a couple rows past that. To show you what I mean, I created a little diagram here.
The offset is going to tell us how many rows to go down from the start. In this case, we’re offsetting ten and we’ll be starting at row 11. Then the fetch next says, “Just bring me back the next five rows, or fetch next ten rows.”
Instead of, as SQL’s default behavior is to fetch everything to the bottom of the result, we’re just going to say, “Just bring back the next couple rows and then call it quits.” When I go in and do a offset, I’m going to show you something real quick here. As you see, we have 19,972 rows. I’m going to show you how when I just do a select, I’m going to offset it by 1,000 rows.
SELECT Title, FirstName, LastName FROM Person.Person ORDER BY LastName OFFSET 1000 ROWS
When I run this, this number should come out to be 18,972 rows.
We actually skipped ahead to row 1,000 and then output from there. The next step in our evolution is to show you how to fetch the next ten rows. Let’s say I want to just eventually fetch down to Baker here. What I can do is add another clause to the end of my statement, which says, “Fetch next ten rows only.”
SELECT Title, FirstName, LastName FROM Person.Person ORDER BY LastName OFFSET 1000 ROWS FETCH NEXT 10 ROWS ONLY
Now when I run this, I should expect to see only ten rows.
Before we wrap up, I think it’s important for you to understand that you can cause your SQL to throw an error if you use the wrong offset value. If you use an offset value of -1, you will throw an error. In other cases, OFFSET and FETCH NEXT will maybe start at the beginning of the result, or in some cases, not return any rows.
What I would do is at this point study the table and make sure that you understand the conditions and what the results are.
All right. Well, I hope to see you next week, because then we’re going to take what we learned today with offset and fetch and apply it to paging. I’m going to teach you how to page through your data using offset and fetch. See you then.