`LIMIT` clause

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

Clause 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;

Result

[
	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. It is important to note that 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;
    }
}

LIMIT clause

Syntax

Examples

Use in pagination

`LIMIT` clause