SQLite Tutorial SQLite Advanced SQLite Database SQLite References

SQLite LIMIT Keyword



The SQLite LIMIT keyword is used to fetch specified number of records from a table. This is useful when the table contains thousands of records and returning a large dataset can impact performance.

Syntax

The syntax for using LIMIT keyword in SQLite is given below:

SELECT column1, column2, ...
FROM table_name
WHERE condition(s)
LIMIT number;

Example:

Consider a database containing a table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • To fetch top 3 records from the Employee table, the following query can be used:

    SELECT * FROM Employee LIMIT 3;
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    2MarryNew York242750
    3JoParis272800
  • To fetch top 3 records where the Age of the employee is greater than 25, the query will be:

    SELECT * FROM Employee 
    WHERE Age > 25
    LIMIT 3;
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    3JoParis272800
    4KimAmsterdam303100
    5RameshNew Delhi283000

Using OFFSET with LIMIT query

The OFFSET value is most often used with the LIMIT query. The OFFSET value allows the user to specify which row to start from to retrieve the data.

Syntax

The syntax for using OFFSET keyword in SQLite is given below:

/* method 1 */
SELECT column1, column2, ...
FROM table_name
WHERE condition(s)
LIMIT fetch_row_count OFFSET offset_row_count;

/* method 2 */
SELECT column1, column2, ...
FROM table_name
WHERE condition(s)
LIMIT offset_row_count, fetch_row_count;

Example:

Consider a database containing a table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • To fetch top 2-5 records from the Employee table, the query is:

    SELECT * FROM Employee LIMIT 4 OFFSET 1;
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    2MarryNew York242750
    3JoParis272800
    4KimAmsterdam303100
    5RameshNew Delhi283000
  • The same result can be achieved by using the below query also.

    SELECT * FROM Employee LIMIT 1, 4;
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    2MarryNew York242750
    3JoParis272800
    4KimAmsterdam303100
    5RameshNew Delhi283000

❮ SQLite Keywords