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 releases before 12c had only the ROWNUM pseudocolumn. The names differ and the job does not: sort the rows a SELECT returns, then keep the first n of them.

TOP, LIMIT, FETCH FIRST and ROWNUM by engine

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

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:

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

Every query carries a sort order, so the rows in each result table are the rows the query returns and not one arbitrary choice among several. The UPDATE and DELETE examples change rows, so reset the table before each of them.

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

TOP caps a result set in SQL Server. It sits directly after SELECT 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

The row count goes between SELECT and the column list:

SELECT TOP (number) columns
FROM table
WHERE condition
ORDER BY column;

Retrieve the three students with the lowest StudentID:

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

Drop the ORDER BY and three rows still come back, but no result table can be written for that query: with a sort order TOP returns the first n ordered rows, and without one it returns n rows in an undefined order.

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 rows is 2.5, and a fractional row count is rounded up to the next whole number[1], so three rows come back rather than two.

WHERE runs before TOP, so the percentage applies to the rows the filter left rather than to the whole table:

SELECT TOP (50) PERCENT * FROM Students
WHERE Age >= 21
ORDER BY Age;

Three students are 21 or over, half of three is 1.5, and that 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:

UPDATE TOP (2) Students
SET Age = 25;

DELETE TOP (2)
FROM Students;

Neither statement gets a result table, because nothing in either one says which two rows it hits. Rows referenced by TOP in an INSERT, UPDATE, MERGE or DELETE are not arranged in any order, and ORDER BY cannot be written directly into those statements[1].

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 and the key names exactly the rows it chose.

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

SELECT columns
FROM table
WHERE condition
ORDER BY column
LIMIT number;

Retrieve the first three students by StudentID:

SELECT * FROM Students
ORDER BY StudentID
LIMIT 3;

The same three rows TOP (3) returned, which is the point of the comparison: another clause name, the same job. A WHERE clause is applied before the cap here as well, so LIMIT counts off whatever the filter left.

LIMIT with OFFSET

OFFSET skips rows before LIMIT starts counting, which is what turns row limiting into pagination. Skip the first two students and return the next two:

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

OFFSET 2 discards rows one and two, and LIMIT 2 returns the two that follow. MySQL 8.4 accepts a second spelling of that same cap, LIMIT 2, 2, in which one clause carries the offset first and the row count second[3]. Either spelling keeps the offset inside the MySQL LIMIT clause. PostgreSQL 17 treats LIMIT and OFFSET as two independent sub-clauses[6], so an offset with no limit is a query on its own there.

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. IBM Db2 11.5 accepts it[9], and so do Oracle 12c and later and PostgreSQL 17. SQL Server 2012 and later accept it only behind an OFFSET, because both clauses are defined as part of ORDER BY there[2].

The FETCH FIRST clause

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

Retrieve the first three students:

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

Three rows out of the same sorted set, and a WHERE clause narrows the rows before the cap exactly as it does with TOP and LIMIT. SQL Server writes the same query as ORDER BY StudentID OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY, with the offset in front and never left out, and TOP cannot appear in the same query expression as that form[2].

FETCH FIRST PERCENT

PERCENT is the exception to that portability: Oracle's row-limiting clause takes a row count or a percentage in the same position[7]. On Oracle, retrieve the first 40% of the table by StudentID:

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

Forty percent of five rows is exactly two, so no rounding is involved. PERCENT is absent from the PostgreSQL 17 and Db2 11.5 fetch syntax and from the SQLite LIMIT syntax, and SQL Server carries it 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]. A query written that way 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 already 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.

Pagination patterns across engines

Row limiting becomes pagination the moment an offset joins it. Page three at ten rows per page means rows 21 to 30:

Two things break paginated results even when the syntax is right. Sort on a column that breaks ties, or the same row turns up on two pages and another on none. And every row an offset skips is produced and thrown away, so page 500 costs the database far more than page two.

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 the wrong clause is the first, and the table at the top of this article is the fix. TOP is SQL Server, LIMIT is MySQL, PostgreSQL and SQLite, and FETCH FIRST n ROWS ONLY is the standard form that Db2 11.5, Oracle 12c and later, and PostgreSQL 17 all take.

Assuming PERCENT travels is the second. TOP (n) PERCENT is SQL Server and FETCH FIRST n PERCENT ROWS ONLY is Oracle[7]; no other engine in this article has a percentage form.

Passing a negative row count is the third. MySQL 8.4 requires both LIMIT arguments to be nonnegative integer constants[3], and SQL Server takes an offset of zero or more and a fetch count of one or more[2].

Limiting without sorting is the last. Every clause here returns an undefined set of rows until an ORDER BY decides which ones, and in PostgreSQL 17 that sort has to constrain the rows into a unique order or two pages of the same query disagree[5].

Practice questions

1. Return the two students under 21 on SQL Server, sorted by age.
2. Update the age of the three youngest students to 30 on MySQL.
3. Delete one student on SQL Server, then write the MySQL equivalent.
4. Return the first 40% of the students aged 20 or over on Oracle, sorted by age.

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.

FAQs

Can TOP, LIMIT and FETCH FIRST cap an UPDATE or a DELETE?

SQL Server has UPDATE TOP (n) and DELETE TOP (n). MySQL 8.4 does the same job with LIMIT on a single-table statement, where it can be combined with ORDER BY, and rejects both clauses in a multiple-table DELETE[4]. FETCH FIRST has no UPDATE or DELETE form: SQL Server defines it as part of the ORDER BY clause[2], which an UPDATE, MERGE or DELETE cannot carry[1].

Can OFFSET be used on its own?

In PostgreSQL 17 it can, because LIMIT and OFFSET are independent sub-clauses. In MySQL 8.4 and SQLite the offset is written inside the LIMIT clause, and in SQL Server it is the other way round, with OFFSET required and FETCH the optional half.

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