Preview
Video Player
00:00
00:00
Paging Through Results
2:33Limiting just by the top set of results is a fine thing, but let's say you wanted to generate a multi page report. Having a blog archive or listing search results in batches of 50 is where you want paging.
Limiting just by the top set
of results is a fine thing.
0:00
But what if we wanted to
generate a multi-page report?
0:05
Let's say we were running a blog and
0:08
we want to present ten posts on a single
page, going through an archive.
0:10
Or in an e-commerce site we want
to show orders in batches of 50.
0:15
Let's take a look on how we do that.
0:20
Here we have a limiting query for orders.
0:23
Remember, you can use the WHERE causes and
0:26
the ORDER BY clauses before the limit
50 to change the order of the results.
0:29
But, let's just focus on whatever order
the database returns the results in
0:35
by default first.
0:39
There are actually hundreds
of orders in this table and
0:42
we can bring them back 50 at a time so
we're not overwhelmed with a wall of data.
0:45
We want to start at the 51st row for
0:51
our second set of data, and
then the 101st row for our third set.
0:54
We want to skip over
rows in increments of 50.
1:00
In other words, we want to use an offset.
1:04
And that's the keyword we use.
1:08
Offset, followed by the number
you want to offset by.
1:10
In our case it's 50 because we want
to start after the 50th record.
1:14
When we run this,
we see it starts at the 51st row, and
1:20
it goes all the way up to the 100th row.
1:24
When we replace the off-set of
50 to 100 and we run this query,
1:28
it starts at the 101st row, and
it goes all the way up to the 150th.
1:33
So in general, you start with your
limiting query, and then you include
1:39
the keyword OFFSET, followed by
the number of rows you want to skip.
1:43
There is a shorthand of this that
you may see where the keyword of
1:48
OFFSET is removed and
the rows skipped is specified first,
1:51
followed by a comma, and then the number
of rows that you want to limit by.
1:55
I find this syntax harder to remember
than using the offset keyword, so
2:01
I tend to use the former
with the keyword offset.
2:05
If you can be more explicit with your
queries, it's better to be that way.
2:09
Other people will thank you for it, and
2:13
you'll thank yourself if you come
back to that query at a later day.
2:15
Once again, while the limit keyword
is implemented in several databases,
2:20
not all of them have this
offset keyword feature.
2:25
Check the teacher's notes to see how
to do this paging in other databases.
2:28