SQLite - LIMIT Clause

Controlling Query Results: The LIMIT Clause

The LIMIT clause is a crucial SQL feature used to constrain the number of rows returned by a query. Its most common application is in implementing pagination, where large result sets are broken down into smaller, manageable ‘pages’ of data.

Syntax

The basic syntax restricts the total number of rows returned.

SELECT column_list
FROM table_name
[WHERE conditions]
[ORDER BY column_name]
LIMIT row_count;

You can also specify a starting point using the OFFSET clause, which skips a specified number of rows before beginning to return results.

SELECT column_list
FROM table_name
[ORDER BY column_name]
LIMIT row_count OFFSET offset_count;

The Critical Role of ORDER BY

Pro-Tip: Always use an ORDER BY clause with LIMIT. Without it, the database makes no guarantee about the order of the rows returned. This means running the same query twice could yield different results, which would break pagination and lead to unpredictable behavior. The ORDER BY clause ensures the result set is stable and predictable.

Example: Implementing Pagination

Let’s use a sample employees table, sorted by name.

ID          NAME            AGE         SALARY
----------  --------------  ----------  ----------
2           Allen Johnson   25          60000.0
7           James Miller    24          52000.0
4           Mark Davis      26          98000.0
1           Paul Smith      32          75000.0
3           Teddy Brown     23          55000.0

To fetch the first page of results, with a page size of 3, we use LIMIT without an offset.

-- Get the first 3 employees, ordered by name
SELECT * FROM employees
ORDER BY name ASC
LIMIT 3;

This produces:

ID          NAME            AGE         SALARY
----------  --------------  ----------  ----------
2           Allen Johnson   25          60000.0
7           James Miller    24          52000.0
4           Mark Davis      26          98000.0

To fetch the second page, we need to skip the first 3 records. We set LIMIT to our page size (3) and OFFSET to the number of records we’ve already seen (3).

-- Skip the first 3 employees and get the next 3
SELECT * FROM employees
ORDER BY name ASC
LIMIT 3 OFFSET 3;

This produces the next set of records:

ID          NAME            AGE         SALARY
----------  --------------  ----------  ----------
1           Paul Smith      32          75000.0
3           Teddy Brown     23          55000.0

Performance Pitfalls and Alternatives

The OFFSET Performance Problem

While LIMIT ... OFFSET is easy to implement, it can become very slow on large tables. When you use a large offset (e.g., OFFSET 100000), the database still has to fetch and count all 100,000 rows internally before it can discard them. This does not scale well.

Alternative: Keyset (or ‘Cursor’) Pagination

A more performant method for ‘infinite scroll’ style pagination is keyset pagination. Instead of an offset, you use a WHERE clause to fetch rows that come after the last item you saw. This requires a unique, ordered column (like a timestamp or an ID).

For example, to get the next page after ‘Mark Davis’ (who has id 4 and was the last on our first page), we could do this:

-- The last name seen was 'Mark Davis'. Fetch the next 3 names after his.
-- This approach uses an index and avoids scanning the entire table.
SELECT * FROM employees
WHERE name > 'Mark Davis'
ORDER BY name ASC
LIMIT 3;

This approach is much more efficient as it can use an index on the name column to jump directly to the correct starting point, avoiding the costly scan of a large offset.

Summary of Best Practices

• Always use ORDER BY with LIMIT to ensure deterministic results.
• Use LIMIT and OFFSET for simple pagination on small-to-medium datasets where performance is not critical.
• For large datasets or infinite-scroll UIs, prefer keyset/cursor pagination over large offsets for better performance.
• Ensure the column(s) in your ORDER BY clause are indexed to speed up sorting.