SQLite Limit Clause

Here we will learn sqlite limit offset clause with example and how to use sqlite limit offset clause to set limit to the records return by a select statement with example.

SQLite Limit Clause

In SQLite LIMIT clause is used to set a limit to the records returned by select statement. 

 

Generally, the SQLite LIMIT clause is used with SELECT statement to retrieve rows from one or more tables in SQLite and limit the number of records returned based on a limit value. In SQLite LIMIT clause will define the maximum number of rows returned by select statement.

Syntax of SQLite Limit Clause

Following is the syntax of using SQLite LIMIT clause with SELECT Statement.

 

SELECT expressions

FROM tables-list

[WHERE conditions]

LIMIT number_rows OFFSET offset_value;

The above SQLite LIMIT clause syntax is having few properties those are

 

  • expressions - It may be no. of columns or expression that you want as a result. 
  • tables-list - It maybe list of the table from which you want result. 
  • WHERE conditions - It is optional. It is one or more conditions to retrieve the result. 
  • LIMIT number_rows - It controls the maximum number of records to retrieve. At most, the number of records specified by number_rows will be returned in the result set. 
  • OFFSET offset_value - It's optional and it defines how many rows to skip at the beginning of the result set based on offset_value.

Suppose if you want to get 5 records starting from 5th row of result set then we need to define offset_value as 5 then our LIMIT clause start picking rows from 5th position.

SQLite Limit Clause Example

To see how to use the SQLite Limit clause to get a limited number of records from the select statement first create an emp_master table and insert some data like as shown below.

 

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER);

 

INSERT INTO emp_master

values (1,'Honey','Patel',10100,1),

(2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,2),

(5,'Shweta','Rana',12000,3),

(6,'sonal','Menpara', 13000,1),

(7,'Yamini','Patel', 10000,2),

(8,'Khyati','Shah', 500000,3);

Now run the following query to check records of emp_master table.

 

sqlite> SELECT * FROM emp_master;

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

1           Honey       Patel       10100       1

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

Now let’s look at the example of the SQLite LIMIT clause with SELECT statement.

 

SELECT first_name, last_name

FROM emp_master

LIMIT 5;

The above SQLite LIMIT clause in a select statement will return the first five records of the emp_master table as shown below.

 

first_name  last_name

----------  ----------

Honey       Patel

Shweta      Jariwala

Vinay       Jariwala

Jagruti     Viras

Shweta      Rana

SQLite Limit Clause with Offset

In SQLite Limit clause the optional OFFSET specifies how many rows to skip at the beginning of the result set. Following is the example of the SQLite LIMIT clause with OFFSET in Select statement.

 

sqlite> SELECT first_name, last_name FROM emp_master LIMIT 4 OFFSET 1;

 

first_name  last_name

----------  ----------

Shweta      Jariwala

Vinay       Jariwala

Jagruti     Viras

Shweta      Rana

First, see the original records of emp_master because of LIMIT 4 OFFSET 1 it will skip first record and after only 4 records are displayed.

 

Following are the some of SQLite Limit clause Offset examples.

 

LIMIT 10 -- returns the first 10 rows (rows 1 - 10)

LIMIT 10 OFFSET 5 -- returns rows 6 - 15

LIMIT 3, 20 -- returns rows 4 - 23 

Notice that the OFFSET value defines how many rows are skipped, not the position of the first row.

Previous
 
Next