SQLite: SELECT LIMIT Statement

This SQLite post explains how to use the SELECT LIMIT statement in SQLite with syntax and examples.

Description

The SQLite SELECT LIMIT statement is used to retrieve records from one or more tables in SQLite and limit the number of records returned based on a limit value.

Syntax

The syntax for the SELECT LIMIT statement in SQLite is:

SELECT expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
LIMIT number_rows OFFSET offset_value;

Parameters or Arguments

expressions

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

ORDER BY expression

It is used in the SELECT LIMIT statement so that you can order the results and target those records that you wish to return.

LIMIT number_rows OFFSET offset_value

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. The first row returned by LIMIT will be determined by offset_value.

Example - Using LIMIT keyword

Let's look at how to use a SELECT statement with a LIMIT clause in SQLite.

For example:

SELECT employee_id, last_name, first_name
FROM employees
WHERE favorite_website = 'AODBA.com'
ORDER BY employee_id DESC
LIMIT 5;

This SQLite SELECT LIMIT example would select the first 5 records from the employees table where the favorite_website is 'AODBA.com'. Note that the results are sorted by employee_id in descending order so this means that the 5 largest employee_id values will be returned by the SELECT LIMIT statement.

If there are other records in the employees table that have a website value of 'AODBA.com', they will not be returned by the SELECT LIMIT statement in SQLite.

If we wanted to select the 5 smallest employee_id values instead of the largest, we could change the sort order as follows:

SELECT employee_id, last_name, first_name
FROM employees
WHERE favorite_website = 'AODBA.com'
ORDER BY employee_id ASC
LIMIT 5;

Now the results would be sorted by employee_id in ascending order, so the first 5 smallest employee_id records that have a favorite_website of 'AODBA.com' would be returned by this SELECT LIMIT statement. No other records would be returned by this query.