SQL guide · Querying

SQL SELECT TOP, LIMIT, FETCH FIRST and ROWNUM

Often you only need the first few rows of a result: the ten best-selling products, the latest five orders, or a quick sample of a table you have never seen. SQL has several ways to say this, and the syntax depends on the database. SQL Server uses SELECT TOP, MySQL, PostgreSQL and SQLite use LIMIT, the SQL standard uses FETCH FIRST ... ROWS ONLY, and older Oracle versions use ROWNUM.

The idea is the same in all of them: the database produces the result, applies ORDER BY, and then stops after the requested number of rows. Because the cut-off comes after sorting, a row limit is only meaningful with an ORDER BY. Without it, you get an arbitrary subset that can differ between runs. Limiting rows is also a cheap way to protect yourself from accidentally pulling millions of rows into a notebook or spreadsheet.

Row limits are the foundation of pagination too. Combine a limit with an offset to skip earlier pages, for example LIMIT 20 OFFSET 40 for the third page of twenty rows. For large tables, offset pagination gets slower the deeper you go, because the database still has to read and discard the skipped rows. Keyset pagination, which filters on the last seen sort value, scales better.

Basic syntax

-- SQL Server / MS Access
SELECT TOP (n) [PERCENT] [WITH TIES] columns FROM table ORDER BY col;

-- MySQL, PostgreSQL, SQLite
SELECT columns FROM table ORDER BY col LIMIT n [OFFSET m];

-- SQL standard (PostgreSQL, Oracle 12c+, SQL Server 2012+)
SELECT columns FROM table ORDER BY col
OFFSET m ROWS FETCH FIRST n ROWS ONLY;

-- Oracle before 12c
SELECT * FROM (SELECT columns FROM table ORDER BY col) WHERE ROWNUM <= n;

Replace n with the number of rows you want and m with the number of rows to skip. TOP goes right after SELECT, while LIMIT and FETCH FIRST go at the end of the query. In SQL Server, parentheses around n are optional for literals but required for variables and expressions.

SQL SELECT TOP / LIMIT examples

Top rows with TOP (SQL Server)

This returns the five most expensive products. TOP is evaluated after ORDER BY, so the sort decides which five rows are kept.

SELECT TOP (5) product_name, unit_price
FROM products
ORDER BY unit_price DESC;

Top rows with LIMIT (MySQL, PostgreSQL, SQLite)

The same question in the LIMIT dialect. The limit is the last clause of the statement, after ORDER BY.

SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 5;

Standard FETCH FIRST with an offset

This skips the first twenty orders and returns the next ten, which is page three of a list with ten rows per page. It works in PostgreSQL, Oracle 12c and later, and SQL Server 2012 and later.

SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC, order_id DESC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

Oracle ROWNUM and percentage limits

In older Oracle versions you sort in a subquery and then filter on ROWNUM. The second query shows SQL Server returning the top ten percent of rows.

SELECT *
FROM (SELECT employee_id, salary FROM employees ORDER BY salary DESC)
WHERE ROWNUM <= 3;

SELECT TOP 10 PERCENT employee_id, salary
FROM employees
ORDER BY salary DESC;

Where SQL dialects differ

SQL Server: Uses TOP (n) and supports TOP n PERCENT and WITH TIES, which includes extra rows that tie with the last row on the sort key. From SQL Server 2012, OFFSET ... FETCH is available but requires an ORDER BY. There is no LIMIT keyword.

MySQL: Uses LIMIT n or LIMIT offset, n. The two-argument form puts the offset first, which is easy to mix up, so LIMIT n OFFSET m is clearer. MySQL does not support FETCH FIRST or TOP.

PostgreSQL: Supports LIMIT n OFFSET m and the standard FETCH FIRST n ROWS ONLY, including WITH TIES from version 13. LIMIT ALL means no limit.

SQLite: Supports LIMIT n OFFSET m and the shorthand LIMIT m, n where the offset comes first. There is no TOP or FETCH FIRST.

Oracle: Oracle 12c and later supports FETCH FIRST n ROWS ONLY and OFFSET. Earlier versions need ROWNUM, which is assigned before sorting, so you must sort in an inner query and apply ROWNUM in the outer one. Putting WHERE ROWNUM <= 3 in the same query as ORDER BY gives wrong results.

Common mistakes

  • Limiting rows without ORDER BY. The database returns whichever rows it finds first, so results are not repeatable.
  • Applying ROWNUM <= n in the same query level as ORDER BY in Oracle. The numbering happens before the sort, so you get n arbitrary rows that are then sorted.
  • Pasting LIMIT into SQL Server or TOP into MySQL. Check the dialect, since each fails with a syntax error in the other.
  • Paginating on a non-unique sort column. Ties can swap places between requests, causing duplicated or missing rows across pages.
  • Using a large OFFSET for deep pages on big tables. The database still reads every skipped row, so prefer keyset pagination.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator