SQLite LIMIT clause is used to limit the data amount returned by the SELECT statement.
Syntax
Following is the basic syntax of SELECT statement with LIMIT clause.
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows]
Following is the syntax of LIMIT clause when it is used along with OFFSET clause.
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num]
SQLite engine will return rows starting from the next row to the given OFFSET as shown below in the last example.
Example
Consider COMPANY table with 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
Following is an example, which limits the row in the table according to the number of rows you want to fetch from 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 certain situations, you may need to pick up a set of records from a particular offset. Here is an example, which picks up 3 records starting from the 3rd 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