SQLite Limit clause

SQLite Limit clause

Grammar

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