May 16, 2021 SQLite
The LIMIT clause of SQLite is used to limit the amount of data returned by the SELECT statement.
The basic syntax of select statements with LIMIT clauses is as follows:
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows]
Here is the syntax when the LIMIT clause is used with the OFFSET clause:
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num]
The SQLite engine returns all rows from the next line until a given OFFSET, as shown in the last instance below.
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's an example that limits the number of rows you want to extract from a table:
sqlite> SELECT * FROM COMPANY LIMIT 6;
This results in the following:
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 may need to start extracting records from a specific offset. Here's an example that extracts 3 records from the third place:
sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
This results in the following:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0