LIMIT

The LIMIT clause is used to limit the number of records returned by a query. It is particularly useful when you want to retrieve a specific number of records from a table.

Syntax

LIMIT @number

Examples

-- Select the first 10 records
SELECT * FROM person LIMIT 10;

-- Start at record 50 and select the following 10 records
SELECT * FROM person LIMIT 10 START 50;

-- Select the first 5 records from the array
SELECT * FROM [1,2,3,4,5,6,7,8,9,10] LIMIT 5 START 4;

[
	5,
	6,
	7,
	8,
	9
]

The LIMIT clause followed by 1 is often used along with the ONLY clause to satisfy the requirement that only up to a single record can be returned.

-- Record IDs are unique so guaranteed to be no more than 1
SELECT * FROM ONLY person:jamie;

-- Error because no guarantee that this will return a single record
SELECT * FROM ONLY person WHERE name = "Jaime";

-- Add `LIMIT 1` to ensure that only up to one record will be returned
SELECT * FROM ONLY person WHERE name = "Jaime" LIMIT 1;

Use in pagination

When using the LIMIT clause, it is possible to paginate results by using the START clause to start from a specific record from the result set. The START count starts from 0.

This pattern is most often used through SDKs to avoid sending messages that exceed a certain size to other pieces of software, and so on.

The following pseudocode demonstrates the most common pattern seen when START and LIMIT are used together.

let current = 0;
loop {
    let query = db.query(SELECT * FROM person START {current} LIMIT 100);
    if query.is_empty() {
        break;
    } else {
        query.send_to_app();
        current += 100;
    }
}