TOP / FETCH FIRST

Not every database uses LIMIT to cap the number of rows a query returns. SQL Server has its own TOP syntax, and the ANSI SQL standard defines FETCH FIRST, which is gaining wide support across databases as the more portable option.

SQL Server's TOP

SQL Server: TOP

SQL

SELECT TOP 10 *
FROM products
ORDER BY price DESC;

Unlike LIMIT, which comes at the *end* of the query, TOP is written immediately after SELECT — right where the row count restriction conceptually applies to the output.

The ANSI standard: FETCH FIRST

FETCH FIRST n ROWS ONLY is the SQL standard’s way of expressing the same idea, and is supported by PostgreSQL, Oracle, DB2, and modern versions of SQL Server:

ANSI-standard FETCH FIRST

SQL

SELECT product_name, price
FROM products
ORDER BY price DESC
FETCH FIRST 10 ROWS ONLY;

It can also be paired with OFFSET for pagination, using standard syntax:

FETCH FIRST with OFFSET

SQL

SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 20 ROWS
FETCH FIRST 10 ROWS ONLY;

Comparing dialects

Here is the exact same query — “get the first 10 products, ordered by price” — written in the native style of five different databases:

Database	Syntax
PostgreSQL	`SELECT * FROM products ORDER BY price DESC LIMIT 10;`
MySQL	`SELECT * FROM products ORDER BY price DESC LIMIT 10;`
SQL Server	`SELECT TOP 10 * FROM products ORDER BY price DESC;`
Oracle (modern, 12c+)	`SELECT * FROM products ORDER BY price DESC FETCH FIRST 10 ROWS ONLY;`
Oracle (legacy, via ROWNUM)	`SELECT * FROM (SELECT * FROM products ORDER BY price DESC) WHERE ROWNUM <= 10;`
SQLite	`SELECT * FROM products ORDER BY price DESC LIMIT 10;`

ROWNUM is a legacy pattern

Older Oracle databases (pre-12c) had no LIMIT-style clause at all — the only option was filtering on the pseudo-column ROWNUM, which is assigned *before* sorting happens, which is why the query above needs a wrapping subquery to sort first and then apply the row-number filter. Oracle 12c and later support the much more readable FETCH FIRST syntax instead.

Portability tip

If you need a query to run unmodified across multiple database engines, FETCH FIRST n ROWS ONLY is the most portable choice, since it is part of the ANSI SQL standard rather than a vendor-specific extension.