SQLite LIMIT Clause
The LIMIT clause in SQLite is used to restrict the number of rows returned by a SELECT statement.
Syntax
The basic syntax of a SELECT statement with a LIMIT clause is as follows:
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]
The syntax when using the LIMIT clause with an OFFSET clause is:
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]
The SQLite engine will return all rows starting from the next row up to the given OFFSET, as shown in the last example below.
Example
Suppose the COMPANY table has the following records:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
Here is an example that limits the number of rows you want to fetch from the table:
sqlite> SELECT * FROM COMPANY LIMIT 6;
This will produce the following result:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
However, in some cases, you might need to fetch records starting from a specific offset. Here is an example that fetches 3 records starting from the third position:
sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
This will produce the following result:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0