SQL 23 🛢️ Limiting Results with LIMIT, OFFSET, and TOP

A query can return millions of rows. The application rarely needs all of them. The user sees a page of ten or twenty results at a time. A dashboard shows the top five performers. A report shows the most recent hundred events. The LIMIT, OFFSET, and TOP clauses are the tools that control how many rows the query returns and which ones.

The previous chapters covered filtering and sorting. This chapter covers pagination — the mechanism that breaks a large result set into manageable pages. Pagination is not just a presentation concern. It is a performance concern, a correctness concern, and a portability concern. The syntax differs between databases. The behavior differs when the sort order is not unique. The performance differs when the offset is large.

Key point: The LIMIT and OFFSET clauses have no effect without an ORDER BY clause. The database is free to return the rows in any order, and the LIMIT returns an arbitrary subset. Pagination without ORDER BY is not pagination. It is a random sample that may overlap between pages. Always pair LIMIT and OFFSET with a deterministic ORDER BY.

---

Why Pagination Matters

A query that returns a million rows is a problem. The database spends time reading the rows. The network spends time transferring them. The application spends time parsing them. The user sees none of them because the interface shows only the first page. Pagination fixes this by limiting the result set to the rows that are actually needed.

The performance problem. Reading a million rows from disk and sending them over the network takes time and resources. Limiting the result to twenty rows reduces the work by orders of magnitude. The database can stop reading once it has the required rows, especially if the sort order is supported by an index.

The memory problem. A result set with a million rows consumes memory on the server, on the network, and on the client. The client application may not even be able to hold the result set in memory. Pagination keeps the result set small.

The user experience problem. A user cannot read a million rows. The user reads a page at a time. Pagination is the mechanism that presents the data in the way the user consumes it.

The correctness problem. Pagination without a deterministic order is incorrect. Page 1 and page 2 may contain overlapping rows. The user may miss rows entirely. The ORDER BY clause makes the pagination deterministic.

The trade-off. Pagination adds complexity. The application must track the current page and the page size. The database must sort the result set before applying the offset. For large offsets, the sort is expensive. The OFFSET clause forces the database to read and discard the first N rows, even if the application never sees them.

---

a. The LIMIT and OFFSET Clauses

The LIMIT clause restricts the number of rows the query returns. The OFFSET clause skips a number of rows before returning the result.

SELECT * FROM customers ORDER BY customer_id LIMIT 10;
SELECT * FROM customers ORDER BY customer_id LIMIT 10 OFFSET 20;

The first query returns the first ten customers. The second returns ten customers starting from the twenty-first (skipping the first twenty).

The LIMIT clause accepts a single argument: the maximum number of rows. The OFFSET clause accepts a single argument: the number of rows to skip. Both clauses are optional. The OFFSET clause without LIMIT skips the first N rows and returns the rest.

SELECT * FROM customers ORDER BY customer_id OFFSET 100;

The query returns all customers except the first hundred. It is rarely useful on its own. Its purpose is to combine with LIMIT.

The LIMIT and OFFSET clauses are supported by PostgreSQL, MySQL, SQLite, and many other databases. They are the most portable form of pagination.

---

b. The TOP Clause and the FETCH FIRST Clause

SQL Server does not support the LIMIT clause. It uses the TOP clause instead. The TOP clause comes immediately after the SELECT keyword.

SELECT TOP 10 * FROM customers ORDER BY customer_id;

The query returns the first ten customers in the order specified by the ORDER BY clause. The TOP clause accepts a number or a percentage.

SELECT TOP 10 PERCENT * FROM customers ORDER BY customer_id;

The query returns the top ten percent of customers.

SQL Server also supports the OFFSET ... FETCH syntax, which is the ANSI SQL standard for pagination. The OFFSET clause skips rows. The FETCH NEXT clause limits the result.

SELECT * FROM customers
ORDER BY customer_id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

The query returns ten customers starting from the twenty-first. The OFFSET ... FETCH syntax is supported by SQL Server, PostgreSQL, Oracle, and other databases. It is the most portable form in theory, but the LIMIT and OFFSET clauses are more widely used in practice.

The ANSI standard also uses the FETCH FIRST clause, which is equivalent to LIMIT.

SELECT * FROM customers ORDER BY customer_id FETCH FIRST 10 ROWS ONLY;

The query returns the first ten customers. The syntax is supported by PostgreSQL, Oracle, and SQL Server.

Database	Syntax
PostgreSQL	LIMIT n OFFSET m or FETCH FIRST n ROWS ONLY
MySQL	LIMIT n OFFSET m or LIMIT m, n
SQLite	LIMIT n OFFSET m
SQL Server	TOP n or OFFSET m ROWS FETCH NEXT n ROWS ONLY
Oracle	FETCH FIRST n ROWS ONLY or OFFSET m ROWS FETCH NEXT n ROWS ONLY

MySQL supports the LIMIT offset, count syntax, where the offset comes first and the count second. It is the reverse of the standard LIMIT count OFFSET offset syntax.

SELECT * FROM customers ORDER BY customer_id LIMIT 20, 10;

The query returns ten customers starting from the twenty-first. The 20 is the offset, and the 10 is the count.

---

c. Pagination and Performance

Pagination is not free. The OFFSET clause forces the database to read and discard the first N rows. For a small offset, the cost is negligible. For a large offset — thousands or millions of rows — the cost is significant.

-- Slow: the database reads and discards 1,000,000 rows
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10 OFFSET 1000000;

The query reads the first million rows, discards them, and returns the next ten. The work is proportional to the offset, not to the result size. On a large table, the query is slow even though it returns only ten rows.

The keyset pagination technique avoids the offset. Instead of skipping rows, the query filters by the last row of the previous page.

-- Fast: the database uses the index to find the starting point
SELECT * FROM orders
WHERE order_date < '2026-01-15'
ORDER BY order_date DESC
LIMIT 10;

The query returns the ten orders older than the last order on the previous page. The WHERE clause uses the index to jump directly to the starting point. The database does not read and discard the preceding rows.

The keyset approach has a limitation: it does not support random access to a specific page. The user cannot jump to page 500. The user can only navigate forward and backward. For many applications, this is acceptable. For applications that require direct page access, the offset-based approach is necessary, and the performance cost must be accepted.

The window function approach is an alternative to the offset for some cases. The ROW_NUMBER() function assigns a number to each row, and the outer query filters by the row number.

SELECT * FROM (
    SELECT
        order_id,
        order_date,
        total,
        ROW_NUMBER() OVER (ORDER BY order_date DESC) AS rn
    FROM orders
) AS numbered
WHERE rn BETWEEN 1000001 AND 1000010;

The query is not faster than the offset-based approach. It reads all the rows and assigns numbers. The advantage is that it can be combined with other window functions. The disadvantage is the same: the cost is proportional to the offset.

Technique	Direct Page Access	Performance
LIMIT + OFFSET	Yes	Slow for large offsets
Keyset pagination	No	Fast for any offset
ROW_NUMBER()	Yes	Slow for large offsets

---

Complete Example Session

This session demonstrates pagination on a small table.

-- ============================================
-- PART 1: THE TABLE
-- ============================================

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date  DATE NOT NULL,
    total       DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders VALUES
    (1001, 1, '2026-01-15', 99.99),
    (1002, 1, '2026-02-20', 149.99),
    (1003, 2, '2026-03-10', 49.99),
    (1004, 3, '2026-04-05', 299.99),
    (1005, 4, '2026-05-12', 199.99),
    (1006, 5, '2026-06-18', 349.99),
    (1007, 1, '2026-07-22', 89.99),
    (1008, 2, '2026-08-30', 129.99),
    (1009, 3, '2026-09-14', 249.99),
    (1010, 4, '2026-10-01', 159.99);

-- ============================================
-- PART 2: LIMIT
-- ============================================

SELECT * FROM orders ORDER BY order_date DESC LIMIT 3;

-- Output:
-- 1010 (2026-10-01), 1009 (2026-09-14), 1008 (2026-08-30)
-- The three most recent orders.

-- ============================================
-- PART 3: LIMIT AND OFFSET
-- ============================================

SELECT * FROM orders ORDER BY order_date DESC LIMIT 3 OFFSET 3;

-- Output:
-- 1007 (2026-07-22), 1006 (2026-06-18), 1005 (2026-05-12)
-- The next three orders.

-- ============================================
-- PART 4: THE PAGINATION PATTERN
-- ============================================

-- Page 1: LIMIT 3 OFFSET 0
-- Page 2: LIMIT 3 OFFSET 3
-- Page 3: LIMIT 3 OFFSET 6
-- Page 4: LIMIT 3 OFFSET 9

SELECT * FROM orders ORDER BY order_date DESC LIMIT 3 OFFSET 9;

-- Output:
-- 1001 (2026-01-15)
-- The last page has only one row.

-- ============================================
-- PART 5: THE OFFSET WITHOUT LIMIT
-- ============================================

SELECT * FROM orders ORDER BY order_date DESC OFFSET 8;

-- Output:
-- 1002 (2026-02-20), 1001 (2026-01-15)
-- All orders except the first eight.

-- ============================================
-- PART 6: THE TOP CLAUSE (SQL SERVER)
-- ============================================

-- SELECT TOP 3 * FROM orders ORDER BY order_date DESC;

-- The syntax returns the three most recent orders.
-- It is not supported by PostgreSQL.

-- ============================================
-- PART 7: THE FETCH FIRST CLAUSE (ANSI)
-- ============================================

SELECT * FROM orders ORDER BY order_date DESC FETCH FIRST 3 ROWS ONLY;

-- Output:
-- 1010, 1009, 1008
-- The ANSI standard syntax.

-- ============================================
-- PART 8: THE OFFSET FETCH CLAUSE
-- ============================================

SELECT * FROM orders
ORDER BY order_date DESC
OFFSET 3 ROWS
FETCH NEXT 3 ROWS ONLY;

-- Output:
-- 1007, 1006, 1005
-- The ANSI standard syntax for pagination.

-- ============================================
-- PART 9: THE MYSQL REVERSED SYNTAX
-- ============================================

-- SELECT * FROM orders ORDER BY order_date DESC LIMIT 3, 3;

-- The first number is the offset.
-- The second number is the count.
-- The query returns the same rows as LIMIT 3 OFFSET 3.

-- ============================================
-- PART 10: THE KEYSET PAGINATION
-- ============================================

-- Page 1
SELECT * FROM orders ORDER BY order_date DESC LIMIT 3;

-- Output: 1010, 1009, 1008

-- Page 2 (using the last order_date from page 1)
SELECT * FROM orders
WHERE order_date < '2026-08-30'
ORDER BY order_date DESC
LIMIT 3;

-- Output: 1007, 1006, 1005

-- The query uses the WHERE clause instead of OFFSET.
-- The index on order_date is used.
-- The performance is constant regardless of the page number.

The ten parts cover the table, LIMIT, LIMIT and OFFSET, the pagination pattern, the offset without limit, the TOP clause, the FETCH FIRST clause, the OFFSET FETCH clause, the MySQL reversed syntax, and keyset pagination.

---

Quick Reference

The Pagination Syntax by Database

Database	Syntax
PostgreSQL	LIMIT n OFFSET m
MySQL	LIMIT n OFFSET m or LIMIT m, n
SQLite	LIMIT n OFFSET m
SQL Server	TOP n or OFFSET m ROWS FETCH NEXT n ROWS ONLY
Oracle	FETCH FIRST n ROWS ONLY or OFFSET m ROWS FETCH NEXT n ROWS ONLY
ANSI standard	OFFSET m ROWS FETCH NEXT n ROWS ONLY

The Clause Order

Clause	Purpose
SELECT	Columns to return
FROM	Table
WHERE	Filter rows
GROUP BY	Group rows
HAVING	Filter groups
ORDER BY	Sort rows
LIMIT	Limit rows
OFFSET	Skip rows

The Pagination Techniques

Technique	Direct Access	Performance
LIMIT + OFFSET	Yes	Slow for large offsets
Keyset pagination	No	Fast for any offset
ROW_NUMBER()	Yes	Slow for large offsets

The Common Mistakes

Mistake	Problem
No ORDER BY	Nondeterministic pagination
LIMIT without ORDER BY	Arbitrary subset
Large OFFSET	Slow query
OFFSET with unstable sort	Overlapping pages

---

Best Practices

✅ Do This:

-- Always pair LIMIT with ORDER BY
SELECT * FROM customers ORDER BY customer_id LIMIT 10          -- ✅

-- Use LIMIT and OFFSET for pagination
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10 OFFSET 20 -- ✅

-- Use keyset pagination for large offsets
SELECT * FROM orders WHERE order_date < '2026-08-30' ORDER BY order_date DESC LIMIT 10 -- ✅

-- Use FETCH FIRST for portability
SELECT * FROM orders ORDER BY order_date DESC FETCH FIRST 10 ROWS ONLY -- ✅

❌ Don’t Do This:

-- Don't use LIMIT without ORDER BY
SELECT * FROM customers LIMIT 10  -- arbitrary                    -- ❌

-- Don't use a large OFFSET on a large table
SELECT * FROM orders ORDER BY order_date LIMIT 10 OFFSET 1000000  -- ❌

-- Don't assume the LIMIT syntax works everywhere
SELECT * FROM customers LIMIT 10  -- fails in SQL Server           -- ❌

-- Don't use an unstable sort
SELECT * FROM customers ORDER BY is_active LIMIT 10                -- ⚠️

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Nondeterministic pages	No ORDER BY	Add the clause
Overlapping pages	Unstable sort	Sort by a unique column
Slow pagination	Large OFFSET	Use keyset pagination
Syntax error	Wrong database syntax	Use the correct clause
Missing rows	Sort column not unique	Add a tiebreaker

---

Real-World Examples

1. First Page

SELECT * FROM customers ORDER BY customer_id LIMIT 10;

2. Second Page

SELECT * FROM customers ORDER BY customer_id LIMIT 10 OFFSET 10;

3. Top 5

SELECT * FROM products ORDER BY price DESC LIMIT 5;

4. Recent 20

SELECT * FROM orders ORDER BY order_date DESC LIMIT 20;

5. TOP Clause (SQL Server)

SELECT TOP 10 * FROM customers ORDER BY customer_id;

6. FETCH FIRST

SELECT * FROM customers ORDER BY customer_id FETCH FIRST 10 ROWS ONLY;

7. OFFSET FETCH

SELECT * FROM customers ORDER BY customer_id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

8. MySQL Reversed

SELECT * FROM customers ORDER BY customer_id LIMIT 20, 10;

9. Keyset Pagination

SELECT * FROM orders WHERE order_date < '2026-08-30' ORDER BY order_date DESC LIMIT 10;

10. Pagination with Total

SELECT * FROM customers ORDER BY customer_id LIMIT 10 OFFSET 0;
SELECT COUNT(*) FROM customers;

---

Visual

The Pagination Pattern

┌──────────────────────────────────────────────┐
│  PAGINATION                                  │
│                                              │
│  Page 1: LIMIT 10 OFFSET 0    → rows 1-10    │
│  Page 2: LIMIT 10 OFFSET 10   → rows 11-20   │
│  Page 3: LIMIT 10 OFFSET 20   → rows 21-30   │
│  ...                                         │
│  Page N: LIMIT 10 OFFSET (N-1)*10            │
│                                              │
│  Requires ORDER BY.                          │
│  The offset is the page number × page size.  │
│                                              │
└──────────────────────────────────────────────┘

The Offset Performance

┌──────────────────────────────────────────────┐
│  OFFSET PERFORMANCE                          │
│                                              │
│  OFFSET 0       → fast (read 10 rows)        │
│  OFFSET 100     → fast (read 110 rows)       │
│  OFFSET 10000   → slow (read 10010 rows)     │
│  OFFSET 1000000 → very slow (read 1000010)   │
│                                              │
│  The cost is proportional to the offset.     │
│  Use keyset pagination for large offsets.    │
│                                              │
└──────────────────────────────────────────────┘

The Keyset Pagination

┌──────────────────────────────────────────────┐
│  KEYSET PAGINATION                           │
│                                              │
│  Page 1:                                     │
│    SELECT * FROM orders                      │
│    ORDER BY order_date DESC                  │
│    LIMIT 10;                                 │
│    → last order_date = '2026-08-30'          │
│                                              │
│  Page 2:                                     │
│    SELECT * FROM orders                      │
│    WHERE order_date < '2026-08-30'           │
│    ORDER BY order_date DESC                  │
│    LIMIT 10;                                 │
│                                              │
│  The WHERE clause uses the index.            │
│  The performance is constant.                │
│                                              │
└──────────────────────────────────────────────┘

The Syntax Comparison

┌──────────────────────────────────────────────┐
│  SYNTAX COMPARISON                           │
│                                              │
│  PostgreSQL / MySQL / SQLite:                │
│    LIMIT 10 OFFSET 20                        │
│                                              │
│  SQL Server:                                 │
│    TOP 10                                    │
│    OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY    │
│                                              │
│  Oracle:                                     │
│    FETCH FIRST 10 ROWS ONLY                  │
│    OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY    │
│                                              │
│  ANSI standard:                              │
│    OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY    │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
LIMIT n	Return at most n rows
OFFSET m	Skip m rows
TOP n	SQL Server only
FETCH FIRST n ROWS ONLY	ANSI standard
OFFSET m ROWS FETCH NEXT n ROWS ONLY	ANSI pagination
LIMIT m, n	MySQL reversed syntax
Requires ORDER BY	Yes
Large offset	Slow
Keyset pagination	Fast alternative
Clause order	After ORDER BY

Key takeaways:

• The LIMIT clause restricts the number of rows the query returns. The OFFSET clause skips a number of rows before returning the result. Both clauses are supported by PostgreSQL, MySQL, and SQLite .
• SQL Server uses the TOP clause instead of LIMIT. The TOP clause comes immediately after the SELECT keyword. It accepts a number or a percentage .
• The ANSI standard uses the OFFSET ... FETCH syntax. The OFFSET clause skips rows. The FETCH NEXT clause limits the result. The syntax is supported by SQL Server, PostgreSQL, and Oracle .
• MySQL supports the reversed LIMIT offset, count syntax. The offset comes first, and the count comes second. It is the reverse of the standard LIMIT count OFFSET offset syntax .
• The LIMIT and OFFSET clauses have no effect without an ORDER BY clause. Without the order, the database returns an arbitrary subset. Pages may overlap or miss rows. The ORDER BY clause makes the pagination deterministic .
• A large OFFSET is slow. The database reads and discards the rows before the offset. The cost is proportional to the offset, not to the result size. Use keyset pagination for large offsets .
• Keyset pagination uses the WHERE clause instead of OFFSET. The query filters by the last row of the previous page. The index on the sort column makes the query fast. The limitation is that the user cannot jump to a specific page directly.

Remember: The LIMIT, OFFSET, and TOP clauses control how many rows the query returns and which ones. LIMIT is the PostgreSQL, MySQL, and SQLite syntax. TOP is the SQL Server syntax. FETCH FIRST and OFFSET FETCH are the ANSI syntax. Always pair the limit with an ORDER BY clause. Without it, the pagination is nondeterministic. Use keyset pagination for large offsets. The offset is slow. The keyset is fast. The order is mandatory.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!