SQL TOP, LIMIT, FETCH FIRST, and ROWNUM Explained

You want the first ten rows out of a large table, and the clause that caps the result set has a different name in every engine. SQL Server writes TOP, MySQL and PostgreSQL write LIMIT, the SQL standard writes FETCH FIRST n ROWS ONLY, and Oracle had no row-limiting clause before 12c, so older releases cap rows with the ROWNUM pseudocolumn. The names differ and the job does not: sort the rows a SELECT returns, then keep the first n of them.

Find your engine in the first column, then write the query.

Two rules cut across all of them. Sort before you cap, because without an ORDER BY the rows you get are an unpredictable subset of the ones that qualify. And skipping is not free: the rows an offset discards still have to be computed inside the server, so a large offset is inefficient[5].

Every example below runs against the same five rows and carries a sort order, so each result table holds the rows its query returns. The UPDATE and DELETE examples change rows, so reset the table after them.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName  VARCHAR(50),
    Age       INT
);

INSERT INTO Students VALUES
    (1, 'John',  'Doe',     20),
    (2, 'Jane',  'Doe',     22),
    (3, 'Sam',   'Smith',   19),
    (4, 'Mike',  'Johnson', 21),
    (5, 'Emily', 'Davis',   23);

TOP, TOP PERCENT, UPDATE TOP and DELETE TOP in SQL Server

TOP caps a result set in SQL Server. It sits between SELECT and the column list and takes either a row count or a percentage. The parentheses are required on an INSERT, UPDATE, MERGE or DELETE, and Microsoft recommends them on a SELECT as well[1].

The TOP clause

Retrieve the three students with the lowest StudentID:

SELECT TOP (3) * FROM Students
ORDER BY StudentID;

TOP PERCENT

PERCENT goes where the row count went and returns a share of the result set. Retrieve the youngest half of the table:

SELECT TOP (50) PERCENT * FROM Students
ORDER BY Age;

Half of five is 2.5, and a fractional row count is rounded up to the next whole number[1], so three rows come back. WHERE runs before TOP, so WHERE Age >= 21 leaves three rows for the percentage and 1.5 rounds up to two.

UPDATE TOP and DELETE TOP

TOP (n) also caps how many rows an UPDATE statement changes or a DELETE statement removes. Neither gets a result table: rows TOP references in an INSERT, UPDATE, MERGE or DELETE are not in any order, and ORDER BY cannot go into those statements[1].

UPDATE TOP (2) Students
SET Age = 25;

DELETE TOP (2)
FROM Students;

To reach particular rows, sort inside a subselect over the primary key and match on it:

DELETE FROM Students
WHERE StudentID IN (
    SELECT TOP (2) StudentID
    FROM Students
    ORDER BY Age
);

That removes Sam and John, the two youngest, because the subselect sorts before it caps. MySQL 8.4 caps a single-table DELETE with LIMIT and ORDER BY, and a multiple-table DELETE with neither[4]. SQL Server takes OFFSET and FETCH in those statements only inside a subquery[2].

LIMIT and OFFSET in MySQL and PostgreSQL

LIMIT goes at the end of the statement rather than straight after SELECT. It is PostgreSQL syntax that MySQL also uses[6], and SQLite takes the same forms[10].

The LIMIT clause

Retrieve the first three students by StudentID:

SELECT * FROM Students
ORDER BY StudentID
LIMIT 3;

The same three rows TOP (3) returned. A WHERE clause narrows the rows before the cap here as well, and both LIMIT arguments have to be nonnegative integer constants[3].

LIMIT with OFFSET

OFFSET skips rows before LIMIT starts counting, which turns row limiting into pagination: page three at ten rows per page is LIMIT 10 OFFSET 20. Skip the first two students and return the next two:

SELECT * FROM Students
ORDER BY StudentID
LIMIT 2 OFFSET 2;

MySQL 8.4 spells the same cap LIMIT 2, 2 as well, with the offset first and the row count second[3]. PostgreSQL 17 treats LIMIT and OFFSET as independent sub-clauses[6], so an offset with no limit is a query on its own there, while SQL Server requires the OFFSET and leaves the FETCH optional[2].

FETCH FIRST n ROWS ONLY, the standard SQL form

SQL:2008 introduced OFFSET and FETCH FIRST for the job LIMIT was already doing[6], which makes it the most portable clause here. The release that first accepted it:

• PostgreSQL, from 8.4
• Oracle, from 12c
• SQL Server, from 2012[2]

IBM Db2 11.5 accepts it too[9]. SQLite has no FETCH FIRST at all[10]. Retrieve the first three students:

SELECT * FROM Students
ORDER BY StudentID
FETCH FIRST 3 ROWS ONLY;

That returns the same three rows TOP (3) and LIMIT 3 returned, and a WHERE clause narrows the rows before the cap as it does with them. SQL Server writes the query as ORDER BY StudentID OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY, because both clauses are part of ORDER BY there, and TOP cannot appear in the same query expression[2].

Oracle is the one engine here whose fetch clause takes a percentage where the row count would go, as FETCH FIRST 40 PERCENT ROWS ONLY[7]. PostgreSQL 17, Db2 11.5 and SQLite have no percentage form at all, and SQL Server carries PERCENT on TOP but not on offset-and-fetch.

ROWNUM and Oracle's row-limiting clause

ROWNUM is a pseudocolumn holding the order in which Oracle selected each row: the first row selected has a ROWNUM of 1, the second has 2. The number is assigned as the rows are produced, so an ORDER BY that follows a ROWNUM condition reorders rows that have already been picked[8]. Such a query returns an arbitrary slice that has then been sorted.

-- Wrong: three arbitrary students, then sorted
SELECT * FROM Students
WHERE ROWNUM <= 3
ORDER BY Age DESC;

-- Right on Oracle 11g and earlier: sort inside a subquery
SELECT * FROM (
    SELECT * FROM Students ORDER BY Age DESC
)
WHERE ROWNUM <= 3;

-- Right on Oracle 12c and later
SELECT * FROM Students
ORDER BY Age DESC
FETCH FIRST 3 ROWS ONLY;

The second and third queries return the three oldest students:

The subquery form works because the ROWNUM values belong to the outer SELECT and are generated after the subquery has ordered the rows. The SQL Language Reference for Oracle AI Database 26ai says the row-limiting clause gives better support for capping a query than ROWNUM does[8], so on a release that has it the third form is the one to write.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Test row-limiting queries in DbSchema

Row-limiting syntax differs by engine, so run each variant against a real table and read the rows before the query ships. Connect DbSchema to the database, write the statement in the DbSchema SQL Editor, and press "Execute Query" to run the statement at the cursor and get the result as a table. The row count, the ordering and whether the cap landed where you meant it to are all in the result pane.

A SELECT run this way reads the database and changes neither it nor the design model file. An UPDATE TOP or a DELETE TOP does change the database, which is the reason to run the matching SELECT in the same editor first. The SQL Editor is part of the free Community Edition.

Common mistakes

• Reaching for a clause the engine does not have. The table at the top says which one it takes.
• Assuming PERCENT travels. Only SQL Server's TOP and Oracle's row-limiting clause have a percentage form.
• Passing a negative row count. SQL Server takes an offset of zero or more and a fetch count of one or more[2].
• Capping without a sort that settles ties. PostgreSQL 17 wants an ORDER BY that constrains the rows into a unique order, or two pages of the same query disagree[5].

TOP, LIMIT, FETCH FIRST n ROWS ONLY and ROWNUM answer one question in four dialects. Pick the clause your engine takes, add a sort order so the rows you get back are the rows you meant, and switch to the offset form when you need pages rather than a slice. Download DbSchema at https://dbschema.com/download.html, connect to SQL Server, MySQL, PostgreSQL, Oracle, Db2 or SQLite, and run each variant in the SQL Editor of the free Community Edition.

Sources

1. SQL Server, TOP
2. SQL Server, ORDER BY
3. MySQL 8.4, SELECT
4. MySQL 8.4, DELETE
5. PostgreSQL 17, LIMIT and OFFSET
6. PostgreSQL 17, SELECT
7. Oracle 19c, row_limiting_clause
8. Oracle AI Database 26ai, ROWNUM Pseudocolumn
9. Db2 11.5, fetch-clause
10. SQLite, select-stmt syntax