Read a Range of Data - LIMIT and OFFSET¶
Now you know how to get all of the rows with SELECT *.
And you also know how to get multiple rows while filtering them using WHERE.
Now let's see how to get only a range of results.
Select with OFFSET and LIMIT¶
Now we can select all of the columns and rows that we want. And we can sort them.
But imagine we are in a user interface showing the results in batches of 3 roots at a time.
Tip
This is commonly called "pagination". Because the user interface would normally show a "page" of a predefined number of roots at a time.
And then you can interact with the user interface to get the next page, and so on.
How do we get the next 3?
SQL with LIMIT and OFFSET¶
Here we are introduced to the new SQL keywords LIMIT and OFFSET.
You can use them in SQL, at the end of the other parts:
SELECT id, nicodemus, english
FROM new_roots
LIMIT 3;
If you try that in DB Browser for SQLite, you will get this result:
SELECT statement against the new_roots table with a LIMIT.It shows the first 3 results, in ** natural order** , that is the order in which data was entered into the database. This is also the order of the PRIMARY KEY if the key is an auto-incremented integer.
How do we select the next 3 rows after that? You combine LIMIT with OFFSET. OFFSET tells you how many records to skip ahead, and LIMIT tells you how many records starting from the offset position you need to retrieve. Here is the SQL to get the next 3 rows (rows 4 to 6):
SELECT id, nicodemus, english
FROM new_roots
LIMIT 3 OFFSET 3;
In DB Browser for SQLite:
SELECT statement against the new_roots table with a LIMIT and a non-zero OFFSET.To prove this is correct, let's get the first 6 rows and see if the results shown previously are correct:
SELECT id, nicodemus, english
FROM new_roots
LIMIT 6 OFFSET 0;
In DB Browser for SQLite:
SELECT statement against the new_roots table with a LIMIT and a zero OFFSET.Combine LIMIT and OFFSET with WHERE and ORDER BY¶
Of course, you can also combine LIMIT and OFFSET with WHERE and ORDER BY:
Suppose that I only want only the words in new_roots that are constructed from the root bm. And I only want the columns nicodemus and english.
SELECT nicodemus, english
FROM new_roots
WHERE root="bm";
In DB Browser for SQLite:
SELECT statement against the new_roots with a WHERE filter.We want to sort these alphabetically in ascending order by english. So we can do this:
SELECT nicodemus, english
FROM new_roots
WHERE root="bm"
ORDER BY english;
In DB Browser for SQLite:
SELECT statement against the new_roots with a WHERE filter and an ORDER BY sort.Now, suppose we want the 6th through the 10th rows of this result. We want the query to retain the sort order and the filtering we specified earlier, while applying the LIMIT and OFFSET to this result. And we can.
The LIMIT we want is 5, since 10-6+1 = 5 is the number of rows we want. You can always count contiguous rows by calculating end-start+1. Here end=10 and start=6.
The OFFSET we want is the number of rows to skip. Well, sort of. Here, we want to skip over 5 rows. We want to start with row 6. The row we want to start with is OFFSET+1 = 5+1 = 6.
Therefore we want:
SELECT nicodemus, english
FROM new_roots
WHERE root="bm"
ORDER BY english
LIMIT 5 OFFSET 5;
In DB Browser for SQLite:
SELECT statement against the new_roots with a WHERE filter and an ORDER BY sort with both a LIMIT and OFFSET.Here is an example of a user interface showing the first page and a later page with limits of 10 results/page. The offset is calculated in code from the desired page number.
Page 1 of 769:
Page 740 of 769:
Recap¶
In conjunction with how you filter the data with WHERE and change the sorting with ORDER BY and other methods of altering the selection, you can limit the query to get at maximum some number of results with LIMIT.
And the same way, you can skip over a number of results with OFFSET.
When your database gets big¶
In these tutorials, we're working with databases that are not very big - and that means, we don't have to worry about the efficiency of our queries.
But you may be interested in growing a database that will contain thousands or even millions of rows, and lots and lots of tables. When/if that happens, it's important to design for efficiency, and one of the best things to do is to implement indexes on your tables. Here's how🚀
What's Next¶
So far, we've focused on getting information out of one table at a time. But what if you want to retrieve information from more than one table? You'll need to know about SQL JOINS statement! 🚀