SQL TOP, LIMIT, FETCH FIRST, and ROWNUM Explained

Every SQL engine limits the rows a SELECT returns with a different clause. SQL Server uses TOP, MySQL and PostgreSQL use LIMIT, standard SQL uses FETCH FIRST n ROWS ONLY, and older Oracle releases use the ROWNUM pseudocolumn. The clause names differ; the job does not. This tutorial gives the syntax, a worked example and the exact rows each clause returns, engine by engine.

Row limiting by engine: TOP, LIMIT, FETCH FIRST, and ROWNUM

Find your engine first, then write the query. The pagination column is the form to reach for when you need page two and beyond, not just the first n rows.

Two rules cut across all of them. Sort before you cap, because without an ORDER BY the engine may return any n rows it likes. And skipping is not free: the server still produces every row an offset discards, so deep pages cost more than shallow ones[3].

Sample database table

Every example below runs against the same five-row Students table in its original state. The UPDATE and DELETE examples do change rows, so assume the table is reset before each query.

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

TOP is how SQL Server and MS Access cap a result set[1]. It sits directly after SELECT, it takes either a row count or a percentage, and - written with parentheses - it also caps the rows an UPDATE, DELETE, INSERT or MERGE statement touches.

SQL SELECT TOP clause

The row count goes between SELECT and the column list:

SELECT TOP number columns
FROM table
WHERE condition;

Retrieve the first three rows of the Students table:

SELECT TOP 3 * FROM Students;

Three rows come back, but which three is undefined: with no sort order in the statement, SQL Server returns whichever three it reaches first.

SQL TOP PERCENT

TOP n PERCENT returns a share of the result set instead of a fixed count:

SELECT TOP percent PERCENT columns
FROM table
WHERE condition;

Retrieve the first 50% of the Students table:

SELECT TOP 50 PERCENT * FROM Students;

There are five rows, and half of five is 2.5. SQL Server rounds a fractional row count up to the next whole number[1], so the query returns three rows rather than two.

SQL TOP PERCENT with WHERE clause

WHERE runs before TOP, so the percentage applies to the filtered rows, not to the whole table. Retrieve the first 50% of students aged 21 or over:

SELECT TOP 50 PERCENT * FROM Students
WHERE Age >= 21;

Three students are 21 or over: Jane, Mike and Emily. Half of three is 1.5, rounded up to two, so two of the three come back.

Using SQL UPDATE TOP

TOP also caps how many rows an UPDATE statement changes. Parentheses are required there, and the rows chosen are undefined[1], so this limits blast radius rather than targeting particular rows.

UPDATE TOP (number) table
SET column1 = value1, column2 = value2, ...
WHERE condition;

Set the age of two students to 25:

UPDATE TOP (2) Students
SET Age = 25;

Two rows change; the other three are untouched.

Using SQL DELETE TOP

The same applies to a DELETE statement: TOP (n) caps how many rows are removed.

DELETE TOP (number)
FROM table
WHERE condition;

Delete two students:

DELETE TOP (2)
FROM Students;

Two rows are gone and three remain. Run the matching SELECT first: TOP gives no control over which rows a DELETE removes.

MySQL and PostgreSQL: LIMIT and OFFSET

LIMIT caps the number of rows a query returns. MySQL, PostgreSQL and SQLite all accept it, and it goes at the end of the statement rather than straight after SELECT.

SQL LIMIT clause

SELECT columns
FROM table
WHERE condition
LIMIT number;

Retrieve the first three students:

SELECT * FROM Students
LIMIT 3;

LIMIT 3 returns three rows, and as with TOP, which three is undefined until the statement sorts them.

SQL LIMIT with WHERE clause

WHERE filters first, then LIMIT caps what is left. Retrieve the first two students under 22:

SELECT * FROM Students
WHERE Age < 22
LIMIT 2;

John, Sam and Mike are under 22. WHERE narrows the set to those three, and LIMIT 2 returns two of them.

SQL LIMIT with OFFSET clause

OFFSET skips rows before LIMIT starts counting, which is what turns row limiting into pagination. In MySQL and SQLite an offset has to follow a LIMIT[2]; PostgreSQL treats the two as independent sub-clauses and accepts an offset on its own[3].

SELECT columns
FROM table
WHERE condition
LIMIT number OFFSET number;

Skip the first two students and return the next two:

SELECT * FROM Students
LIMIT 2 OFFSET 2;

OFFSET 2 discards rows one and two, and LIMIT 2 returns the two that follow.

Standard SQL: FETCH FIRST n ROWS ONLY

FETCH FIRST n ROWS ONLY is the SQL:2008 standard form, and it is the most portable clause in this article. IBM Db2 accepts it, so do Oracle 12c and later[4], PostgreSQL[3], and SQL Server 2012 and later.

SQL FETCH FIRST n ROWS ONLY clause

SELECT columns
FROM table
WHERE condition
ORDER BY column
FETCH FIRST n ROWS ONLY;

Retrieve the first three students:

SELECT * FROM Students
FETCH FIRST 3 ROWS ONLY;

Three rows come back. SQL Server writes the same idea as OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY, and there a sort order is not optional: offset and fetch are defined as part of the ORDER BY clause[8], and FETCH cannot appear without OFFSET.

SQL FETCH FIRST PERCENT

PERCENT is the exception to that portability: it is an Oracle extension to the row-limiting clause[4]. PostgreSQL, IBM Db2 and SQLite have no percentage form at all, and SQL Server has PERCENT on TOP but not on its offset-and-fetch syntax[8].

SELECT columns
FROM table
ORDER BY column
FETCH FIRST percent PERCENT ROWS ONLY;

On Oracle, retrieve the first 40% of the Students table:

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

Forty percent of five rows is exactly two, so two rows come back. Oracle's documentation asks for a sort order alongside the row-limiting clause[4], or the percentage is taken from an unordered set.

SQL FETCH FIRST with WHERE clause

WHERE filters, then FETCH FIRST caps - the same order as TOP and LIMIT. Retrieve the first two students aged 21 or over:

SELECT * FROM Students
WHERE Age >= 21
FETCH FIRST 2 ROWS ONLY;

Jane, Mike and Emily are 21 or over, and the clause returns two of the three.

Oracle: ROWNUM and the row-limiting clause

ROWNUM is a pseudocolumn Oracle assigns as rows are produced, before any sort is applied[5]. That order of operations is the trap: a query that filters on ROWNUM and then sorts returns an arbitrary slice that has been sorted, not the top rows of the sorted set.

-- 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;

Sorting inside a subquery works because the outer ROWNUM values are generated after the rows have already been ordered[5]. On Oracle 12c and later there is no reason to hand-write either form: the row-limiting clause does the same job[4] in one statement.

Pagination patterns across engines

Row limiting becomes pagination the moment an offset joins it. Here is page three at ten rows per page - rows 21 to 30 - in each engine.

Two things break paginated results more often than the syntax does. Sort on a column that breaks ties, or the same row turns up on two pages and another on none. And a large offset still costs the server every row it then throws away, so page 500 is far more expensive than page two[3].

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 the safest habit is to run each variant against a real table and read the rows that come back before the query reaches production. In DbSchema you open a connection, run the statement in the SQL editor, and check the returned result grid - the row count, the ordering, and whether the limit landed where you expected. The SQL editor is part of the free Community Edition[9].

Common mistakes

1. Reaching for the wrong clause. TOP is SQL Server and MS Access, LIMIT is MySQL, PostgreSQL and SQLite, and FETCH FIRST n ROWS ONLY is the standard form that Db2, Oracle 12c and later, PostgreSQL and SQL Server 2012 and later all accept.
2. Assuming an offset always needs a limit. MySQL and SQLite do require one; PostgreSQL treats LIMIT and OFFSET as two independent sub-clauses and accepts an offset alone[3].
3. Assuming PERCENT is portable. TOP n PERCENT is SQL Server only, FETCH FIRST n PERCENT ROWS ONLY is Oracle only[4], and no other engine here has a percentage form.
4. Assuming a negative argument always raises an error. MySQL, PostgreSQL, Db2 and SQL Server all reject one, but Oracle treats a negative offset as zero and SQLite treats a negative limit as no limit at all[7].
5. Limiting without sorting. Every clause in this article returns an undefined set of rows until an ORDER BY makes the choice deterministic.

FAQs

Practice questions

1. Write a SQL Server query that returns the first two students under 21.
2. Write a MySQL statement that updates the age of the first three students to 30.
3. Write a SQL Server statement that deletes one student, then write the MySQL equivalent.
4. Write an Oracle query that returns the first 40% of students aged 20 or over, sorted by age.

Conclusion

TOP, LIMIT, FETCH FIRST n ROWS ONLY and ROWNUM answer one question in four dialects. Pick the clause your engine accepts, 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[9] to run these queries against your own database, compare how each engine answers them, and check the returned rows before the query ships. Connecting to your database and writing SQL in the editor are both part of the free Community Edition.

Sources

1. TOP (Transact-SQL) — Microsoft Learn
2. MySQL 8.4 Reference Manual — SELECT Statement
3. PostgreSQL Documentation — LIMIT and OFFSET
4. Oracle Database SQL Language Reference — SELECT (row_limiting_clause)
5. Oracle Database SQL Language Reference — ROWNUM Pseudocolumn
6. IBM Db2 11.5 — fetch-first-clause
7. SQLite — SELECT, The LIMIT clause
8. ORDER BY Clause (Transact-SQL) — Microsoft Learn
9. Download DbSchema
10. MySQL 8.4 Reference Manual — DELETE Statement