SQLite – LIMIT Clause

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

Leave a Reply