SQLite - LIMIT Clause


Advertisements

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

Useful Video Courses


Video

Android SQLite Programming for Beginners

25 Lectures 4.5 hours

Sandip Bhattacharya

Video

Local SQLite Database with Node for beginners

17 Lectures 1 hours

Laurence Svekis

Video

Angular 12, Python Django & SQLite Full Stack Web Development

5 Lectures 51 mins

Vinay Kumar

Advertisements