LIMIT and OFFSET Results

The SELECT statement is your window into the data, but what happens when that window shows you 10,000 rows when you only need the first 10? Or when your application needs to display results in manageable pages, each showing just the next batch? Fetching everything is wasteful, slow, and can overwhelm both your database and your user interface. The answer is LIMIT and OFFSET — two simple clauses that give you surgical control over which rows a query returns. This lesson teaches you how to use them to paginate results, build "load more" buttons, and keep your queries lean and predictable.

The problem this lesson solves

Imagine you have a table with millions of rows, and your application needs to display products to a user. If every request returns all rows, you are sending megabytes of data over the network, consuming server memory, and forcing the database to process and transmit data that will never be seen. This is a classic performance bottleneck that affects response times, user experience, and even database stability.

Beyond performance, consider usability: Users rarely need every record at once. They want the first twenty results, then the next twenty, and so on. Without a way to slice results, you cannot build a pagination control, an infinite scroll, or a "top 10" dashboard widget efficiently.

This is precisely the problem that LIMIT and OFFSET solve. They allow you to: - Cap the number of rows returned, keeping responses small and fast. - Skip a specific number of rows to fetch the next page of data. - Create stable, predictable API responses where each request returns a known slice of the dataset.

Mastering these clauses is a fundamental skill for anyone working with databases in real-world applications. Without them, your queries will be inefficient, your endpoints will be sluggish, and your users will feel it.

Core concept / mental model

Think of a query result as a book. The SELECT statement with its WHERE, ORDER BY, and other clauses composes the story — the complete set of rows that match your criteria. But you don't want to read the entire book at once; you want a chapter, or even a paragraph.

• LIMIT n acts like turning to a specific page in the table of contents: “Give me only the first n rows of the result set.”
• OFFSET m asks the database to skip the first m rows and return the rest. Together, LIMIT and OFFSET let you flip through the book page by page.

A helpful analogy: imagine a conveyor belt carrying sorted boxes. OFFSET tells the operator to push aside the first m boxes, and LIMIT tells them to grab the next n boxes. What you receive is a contiguous batch from the original stream.

Definitions

• Result set: The complete collection of rows a query returns before any slicing.
• LIMIT: A clause that restricts the number of rows returned. The syntax is LIMIT n where n is a non-negative integer.
• OFFSET: A clause that skips a number of rows before starting to return results. Syntax: OFFSET m.
• Pagination: The technique of breaking a large result set into smaller, sequential pages using LIMIT and OFFSET.
• Ordering is critical: The order of rows in the result set is not guaranteed unless you use ORDER BY. Without it, LIMIT and OFFSET can return arbitrary rows.

A word of caution

While the mental model is simple, the practical implications matter. The database still has to generate the entire result set before applying LIMIT and OFFSET (for typical queries). So, offsetting millions of rows can be slow — it's not a magic fast-forward button. Later sections will address this performance caveat.

How it works step by step

Let's walk through the mechanics of LIMIT and OFFSET in a PostgreSQL query. The clauses appear at the very end of a SELECT statement, after ORDER BY.

1. Start with a complete query

Always begin with the query that returns the full result set you care about. For example:

SELECT product_id, name, price
FROM products
ORDER BY price DESC;

This returns all products ordered by price from highest to lowest.

2. Add LIMIT

Append LIMIT 10 to fetch only the top 10 most expensive products:

SELECT product_id, name, price
FROM products
ORDER BY price DESC
LIMIT 10;

3. Add OFFSET to skip

To get the next page of 10 products, skip the first 10 rows:

SELECT product_id, name, price
FROM products
ORDER BY price DESC
LIMIT 10 OFFSET 10;

4. Combine with other clauses

The full clause order is WHERE → GROUP BY → HAVING → ORDER BY → LIMIT → OFFSET. OFFSET can also be written without LIMIT — useful if you want to skip rows but return the rest.

5. The database execution perspective

Understand what the server does: it executes the query to produce the intermediate result set, then sorts (if ORDER BY present), then drops OFFSET rows, and finally returns LIMIT rows. This is why indexes alone don't make offset pagination fast for large offsets — the sort and scan still process all preceding rows.

Key syntax points

• LIMIT must be a non-negative integer (or a parameter in prepared statements).
• OFFSET accepts only non-negative integers.
• Both LIMIT and OFFSET can be used independently or together.
• They are not SQL-standard; they are PostgreSQL extensions (though many databases offer similar syntax).

Hands-on walkthrough

Let's put this into practice with a realistic table. We'll create a sample database and run queries to see LIMIT and OFFSET in action.

Setup

First, create a table and insert some data:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary NUMERIC
);

INSERT INTO employees (name, salary) VALUES
('Alice', 75000),
('Bob', 82000),
('Carol', 68000),
('Dave', 90000),
('Eve', 72000),
('Frank', 95000),
('Grace', 88000);

Example 1: Fetch the top 3 highest-paid

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Expected output:

Frank | 95000
Dave  | 90000
Grace | 88000

Example 2: Pagination — page 2, 2 rows per page

Page 1: LIMIT 2 OFFSET 0 Page 2: LIMIT 2 OFFSET 2

-- Page 1 (first two highest paid)
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 2 OFFSET 0;

-- Page 2 (skip first two, get next two)
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 2 OFFSET 2;

Page 1 output:

Frank | 95000
Dave  | 90000

Page 2 output:

Grace | 88000
Bob   | 82000

Example 3: Skip rows without a limit

Get all employees except the top two highest paid:

SELECT name, salary
FROM employees
ORDER BY salary DESC
OFFSET 2;

Expected output:

Grace | 88000
Bob   | 82000
Alice | 75000
Eve   | 72000
Carol | 68000

Example 4: Use LIMIT and OFFSET with parameters (in Python)

Here's how you might use them in a Python application with psycopg2:

import psycopg2

conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()

page_size = 2
page_number = 2  # second page

offset = (page_number - 1) * page_size
cur.execute(
    """
    SELECT name, salary
    FROM employees
    ORDER BY salary DESC
    LIMIT %s OFFSET %s
    """,
    (page_size, offset)
)

rows = cur.fetchall()
for row in rows:
    print(row)

cur.close()
conn.close()

Expected output:

('Grace', Decimal('88000'))
('Bob', Decimal('82000'))

Compare options / when to choose what

LIMIT and OFFSET are not the only way to paginate. In PostgreSQL, you have alternatives like keyset pagination (also called seek method) and window functions. The following table compares the options:

When to choose what?

• Use LIMIT/OFFSET when your dataset is small to medium, or when you need to jump to a specific page number (e.g., a classic pagination bar).
• Use keyset pagination when you have a large dataset, an API for continuous scrolling, and you care about performance and consistency.
• Use window functions for analytical queries where you need to rank or number rows across pages.

Variations of LIMIT/OFFSET

• LIMIT ALL returns all rows — equivalent to omitting LIMIT.
• OFFSET 0 is the default; you don't need to specify it.
• You can use expressions for LIMIT and OFFSET, but they must evaluate to integers.
• In PostgreSQL, you can write LIMIT 10 OFFSET 10 or LIMIT (10) OFFSET (10) — parentheses are optional.

Troubleshooting & edge cases

Even simple syntax can trip you up. Here are common issues and how to solve them.

1. Missing ORDER BY causes unpredictable results

-- BAD: No ORDER BY
SELECT * FROM products LIMIT 5;
SELECT * FROM products LIMIT 5 OFFSET 10;

The two pages may overlap or skip rows because the database doesn't guarantee order. Fix: Always add an ORDER BY clause with a unique column (or a unique combination) to ensure stable pagination.

2. Negative values cause errors

SELECT * FROM products LIMIT -1;

PostgreSQL throws an error: argument of LIMIT must not be negative. The same applies to OFFSET. Fix: Ensure your code never passes negative numbers. Validate input in your application.

3. Large offsets hurt performance

If you request LIMIT 10 OFFSET 100000, the server must generate and discard 100,000 rows before returning 10. This becomes slower as the offset grows. Fix: For deeply nested pages, use keyset pagination or add a filter on an indexed column (e.g., WHERE id > last_seen_id).

4. Data changes between page requests

If rows are inserted or deleted while a user paginates, you might see duplicates or missing rows. LIMIT/OFFSET doesn't lock the table. Fix: Use an ORDER BY on a unique key and consider using keyset pagination to avoid this issue.

5. OFFSET without LIMIT can be confusing

SELECT * FROM table OFFSET 5; skips the first 5 rows and returns the rest. That's valid, but verify that's what you intend — it's easy to forget LIMIT when you only meant to skip.

6. Mixing order of clauses

SELECT * FROM table LIMIT 5 WHERE column = 'x'; -- Syntax error

LIMIT and OFFSET must come after ORDER BY and at the very end. The correct order is WHERE → GROUP BY → HAVING → ORDER BY → LIMIT → OFFSET.

7. Using LIMIT in subqueries

In a subquery, you may need LIMIT to pick a specific row, but be aware of the semantics: without ORDER BY, the row is arbitrary. Always order subqueries if you need a deterministic choice.

What you learned & what's next

You've mastered the art of limit and offset result sets. Here's a recap of what we covered:

• You understand the problem of returning entire datasets and how LIMIT and OFFSET fix it by capping and skipping rows.
• You built a mental model of slicing a result set — like flipping pages in a book.
• You learned the exact syntax and clause order, and you can combine these clauses with ORDER BY and WHERE.
• You practiced with complete SQL examples and a Python integration, seeing real output.
• You compared LIMIT/OFFSET with alternatives like keyset pagination and know when to choose each.
• You identified common pitfalls — from missing ORDER BY to performance issues with large offsets — and how to avoid them.

Next step in your learning path

Now that you can control result size, the next logical skill is aggregating data. You'll learn how to group rows and compute summary statistics using GROUP BY and aggregate functions like COUNT, SUM, and AVG. These let you answer questions like "How many orders per customer?" or "What's the average salary per department?" — making your SQL queries truly powerful.

Pro tip: As you move forward, keep the pagination techniques fresh. They'll show up again when you design APIs and handle large data sets. Practice combining LIMIT and OFFSET with GROUP BY results to paginate summary tables as well.