SQLite Limit Clause
SQLite'sLIMITThe clause is used to limit the amount of data 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 following is the syntax when the LIMIT clause is used together with the OFFSET clause:
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num]
The above SQL statement selects data from columns such as column1, column2, and other columns from the table_name table, and limits the number of rows returned and the starting row. It is commonly used for paginated display of data, where LIMIT is used to control the number of rows displayed per page, and OFFSET is used to control which row the data display starts from.
-
SELECT column1, column2, columnN: This part specifies which columns to select from the database.column1、column2andcolumnNare the column names, and you can replace them with actual column names.columnNThis means there may be more columns; hereNindicates that the number of columns is variable. -
FROM table_name: This part specifies which table to query data from.table_nameshould be replaced with the actual table name. -
LIMIT [no of rows]: This part is used to limit the number of rows in the query result.[no of rows]is a number that represents the maximum number of rows you want returned. For example, if you writeLIMIT 10, then the query will return at most 10 rows of data. -
OFFSET [row num]: This part is used to specify the starting point of the query result.[row num]is a number that indicates which row to start returning data from. For example, if you writeOFFSET 5, then the query will skip the first 5 rows and start returning data from the 6th row.
Example
Assume 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 extract 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 may need to extract records starting from a specific offset. Here is an example that extracts 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.0Other Extensions