A SELECT statement can easily return thousands or millions of rows, but in most real applications you only need a handful of them at a time — the ten cheapest books, the top five customers, the most recent orders, or a single page of search results. Downloading everything and discarding the rest wastes memory, network bandwidth, and time.

SQL provides a way to tell the database, “return only the first N rows of the result.” The keyword is not the same in every product — you will see LIMIT, TOP, and FETCH FIRST — but the idea is identical. In this tutorial you will learn how to limit rows portably, how to combine limiting with ORDER BY, how to page through a large result set with OFFSET, and which dialect uses which keyword.

Prerequisites

To follow along, you need:

• A working SQL database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).
• A client where you can execute SQL statements, such as DBeaver, psql, mysql, or sqlite3.
• Basic familiarity with SELECT, WHERE, and ORDER BY.

If you do not have a sample database yet, the setup script in the next section creates everything the tutorial needs.

Sample Database

We reuse the small online bookstore schema from the previous tutorials in this series. It contains two tables: authors and books.

CREATE TABLE authors
(
    author_id   INTEGER PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    country     VARCHAR(50)
);

CREATE TABLE books
(
    book_id      INTEGER PRIMARY KEY,
    title        VARCHAR(200)  NOT NULL,
    author_id    INTEGER       NOT NULL,
    category     VARCHAR(50),
    price        DECIMAL(8, 2) NOT NULL,
    stock        INTEGER       NOT NULL,
    published_on DATE,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id) REFERENCES authors (author_id)
);

INSERT INTO authors (author_id, author_name, country)
VALUES (1, 'Jane Austen', 'United Kingdom'),
       (2, 'Haruki Murakami', 'Japan'),
       (3, 'Chimamanda Ngozi Adichie', 'Nigeria'),
       (4, 'Gabriel Garcia Marquez', 'Colombia');

INSERT INTO books (book_id, title, author_id, category, price, stock,
                   published_on)
VALUES (1, 'Pride and Prejudice', 1, 'Classic', 12.50, 20, '1813-01-28'),
       (2, 'Emma', 1, 'Classic', 10.00, 0, '1815-12-23'),
       (3, 'Norwegian Wood', 2, 'Fiction', 15.75, 12, '1987-09-04'),
       (4, 'Kafka on the Shore', 2, 'Fiction', 18.20, 5, '2002-09-12'),
       (5, 'Half of a Yellow Sun', 3, 'Historical', 16.00, 8, '2006-08-11'),
       (6, 'Americanah', 3, 'Contemporary', 14.50, 3, '2013-05-14'),
       (7, 'One Hundred Years of Solitude', 4, 'Classic', 22.00, 25,
        '1967-05-30'),
       (8, 'Love in the Time of Cholera', 4, 'Classic', 19.99, 0, '1985-09-05');

Eight books is enough to make the effect of row-limiting easy to see and check by hand.

Basic Syntax

Row limiting is written in different ways depending on the database, but every dialect requires two things to be useful:

1. A defined order for the rows (via ORDER BY), so that “the first N rows” has a meaningful answer.
2. A limit value — the maximum number of rows to return.

The three main forms are shown below.

PostgreSQL, MySQL, MariaDB, SQLite

SELECT column_list
FROM table_name
ORDER BY sort_expression LIMIT row_count;

SQL Server

SELECT TOP(row_count)
       column_list
FROM table_name
ORDER BY sort_expression;

Oracle Database and ANSI SQL

SELECT column_list
FROM table_name
ORDER BY sort_expression
    FETCH FIRST row_count ROWS ONLY;

FETCH FIRST ... ROWS ONLY is defined by the SQL standard and is also supported by PostgreSQL and modern versions of MySQL, MariaDB, and SQL Server. When portability matters, prefer this form.

Important: without an ORDER BY clause, “the first N rows” is not really defined. The database is free to return any N rows it wants, and that choice can change between runs. Always combine row limiting with an explicit order when the answer must be meaningful.

Practical Example

The bookstore manager wants to display the three cheapest books on the home page.

SELECT book_id,
       title,
       price
FROM books
ORDER BY price ASC LIMIT 3;

Reading the query in logical execution order:

1. FROM books — start from every row in the books table.
2. ORDER BY price ASC — sort the rows from the smallest to the largest price.
3. LIMIT 3 — keep only the first three rows of that sorted result.
4. SELECT book_id, title, price — return the three columns for those rows.

Expected Result

book_id	title	price
2	Emma	10.00
1	Pride and Prejudice	12.50
6	Americanah	14.50

Additional Examples

1. Top N in Descending Order

To show the three most expensive books, keep the same LIMIT and reverse the sort direction.

SELECT book_id,
       title,
       price
FROM books
ORDER BY price DESC LIMIT 3;

Expected result:

book_id	title	price
7	One Hundred Years of Solitude	22.00
8	Love in the Time of Cholera	19.99
4	Kafka on the Shore	18.20

2. Portable Syntax with FETCH FIRST

The same result written in ANSI-standard form:

SELECT book_id,
       title,
       price
FROM books
ORDER BY price DESC
    FETCH FIRST 3 ROWS ONLY;

This runs on PostgreSQL, Oracle Database, SQL Server 2012+, MySQL 8.0.31+, MariaDB 10.6+, and SQLite 3.30+.

3. SQL Server TOP

SQL Server uses TOP in the SELECT list. Parentheses around the count are recommended and required when the count is an expression or parameter.

SELECT TOP(3)
       book_id,
       title,
       price
FROM books
ORDER BY price DESC;

4. Pagination with OFFSET

To display page 2 of a list where each page shows three books, skip the first three rows and take the next three.

-- PostgreSQL, MySQL, MariaDB, SQLite
SELECT book_id,
       title,
       price
FROM books
ORDER BY price ASC, book_id ASC LIMIT 3
OFFSET 3;

Portable version:

SELECT book_id,
       title,
       price
FROM books
ORDER BY price ASC, book_id ASC
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

Expected result (rows 4 through 6 of the price-ascending list):

book_id	title	price
3	Norwegian Wood	15.75
5	Half of a Yellow Sun	16.00
4	Kafka on the Shore	18.20

Notice the tie-breaker book_id ASC in the ORDER BY. Without it, two books that share the same price could switch positions between page 1 and page 2, so a row might appear twice or not at all.

5. Limiting After Filtering

Row limiting is applied after WHERE, so you can safely combine both.

SELECT title,
       price,
       stock
FROM books
WHERE category = 'Classic'
  AND stock > 0
ORDER BY price ASC LIMIT 2;

“Show me the two cheapest classic books that are still in stock.”

Expected result:

title	price	stock
Pride and Prejudice	12.50	20
One Hundred Years of Solitude	22.00	25

6. Sampling Without an ORDER BY

Sometimes you genuinely do not care which rows you get — you just want a small, cheap sample while exploring a large table:

SELECT book_id,
       title
FROM books LIMIT 5;

This is fine for ad-hoc inspection, but do not rely on the row order or on which rows you get. For anything a user will see, add an ORDER BY.

7. Using LIMIT from Java

When your application shows one page of results at a time, pass the page size and offset as parameters — never build the SQL by concatenating them.

String sql = """
    SELECT book_id, title, price
    FROM books
    WHERE category = ?
    ORDER BY price ASC, book_id ASC
    LIMIT ? OFFSET ?
    """;

try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, category);
statement.setInt(2, pageSize);
statement.setInt(3, pageSize * (pageIndex - 1));

    try (ResultSet resultSet = statement.executeQuery()) {
        while (resultSet.next()) {
            long bookId = resultSet.getLong("book_id");
            String title = resultSet.getString("title");
            BigDecimal price = resultSet.getBigDecimal("price");
            // process the row...
        }
    }
}

Parameterizing the values keeps the query cache-friendly and eliminates SQL-injection risk.

Common Mistakes

Using LIMIT Without ORDER BY

Incorrect:

SELECT title, price
FROM books LIMIT 3;
-- "Give me the three cheapest books."

The query does not ask for the cheapest books — it asks for any three. The database is free to return whichever three rows are convenient. Add the sort:

SELECT title, price
FROM books
ORDER BY price ASC LIMIT 3;

Forgetting a Tie-Breaker When Paging

If several rows share the same value in the sort column, their relative order is undefined. During pagination this can cause rows to appear twice or be skipped.

Fragile:

SELECT book_id, title, price
FROM books
ORDER BY price ASC LIMIT 3
OFFSET 3;

Reliable:

SELECT book_id, title, price
FROM books
ORDER BY price ASC, book_id ASC LIMIT 3
OFFSET 3;

Mixing Dialects in the Same Statement

SELECT TOP 3 ... LIMIT 3 is not valid SQL in any product. Choose one form based on your database:

• PostgreSQL, MySQL, MariaDB, SQLite → LIMIT (or FETCH FIRST).
• SQL Server → TOP (or OFFSET ... FETCH NEXT).
• Oracle Database → FETCH FIRST (or ROWNUM in older versions).

Assuming LIMIT Runs Before WHERE

LIMIT is applied to the already-filtered, already-sorted result set, not to the raw table. LIMIT 10 does not mean “read only 10 rows from disk” — the database may still scan the whole table if there is no supporting index. Use WHERE to reduce the working set, and consider indexes on the filter and sort columns for large tables.

Database Compatibility

Database	Preferred syntax
PostgreSQL	LIMIT n [OFFSET m] or FETCH FIRST n ROWS ONLY
MySQL	LIMIT n [OFFSET m] or LIMIT m, n
MariaDB	LIMIT n [OFFSET m] or LIMIT m, n
SQLite	LIMIT n [OFFSET m]
SQL Server	SELECT TOP (n) ... or OFFSET m ROWS FETCH NEXT n ROWS ONLY
Oracle Database	FETCH FIRST n ROWS ONLY (12c+); ROWNUM <= n on older versions

Notes:

• FETCH FIRST / FETCH NEXT is part of ANSI SQL and is the most portable option across modern versions.
• LIMIT m, n (MySQL and MariaDB shorthand) treats the first value as the offset and the second as the count. This ordering is easy to mix up; prefer LIMIT n OFFSET m for clarity.
• Older Oracle Database versions (pre-12c) do not support FETCH FIRST. Use WHERE ROWNUM <= n around a sorted subquery.
• SQL Server requires an ORDER BY clause when using OFFSET ... FETCH NEXT.

When in doubt, consult the documentation for the specific database and version you are using.

Best Practices

• Combine row limiting with an explicit ORDER BY whenever the identity of the returned rows matters.
• Add a deterministic tie-breaker, such as the primary key, so that ordering and pagination remain stable across runs.
• Prefer the portable FETCH FIRST n ROWS ONLY form when writing SQL that must run on more than one database.
• Use parameters for the limit and offset values, especially in application code — do not concatenate them into the SQL string.
• Consider indexes on filter and sort columns for large tables, and measure with your database’s execution-plan tool before assuming a benefit.
• Be careful with very large OFFSET values. Most databases still read and discard all the skipped rows, so deep pagination can be surprisingly slow. Keyset pagination — filtering with WHERE (sort_key, id) > (last_sort_key, last_id) — is often faster for large data sets.

Conclusion

You learned how to restrict the number of rows a query returns using LIMIT, TOP, and FETCH FIRST, how to combine row limiting with ORDER BY and OFFSET for pagination, and how the syntax differs across PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, and SQLite. Always sort explicitly and add a deterministic tie-breaker before you rely on “top N” or paged results.