SQL TOP, LIMIT and FETCH FIRST

Limiting rows is useful when you only need a small result set: the newest orders, the most expensive products, or the first page of a search result.

Syntax

Different database systems use different keywords for this task.

-- SQL Server
SELECT TOP 10 column_name
FROM table_name;

-- MySQL, PostgreSQL and SQLite
SELECT column_name
FROM table_name
LIMIT 10;

-- Standard-style syntax supported by several databases
SELECT column_name
FROM table_name
FETCH FIRST 10 ROWS ONLY;

Example

Get the five most expensive products:

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

Use ORDER BY when the order matters. Without it, the database can return any matching rows, which is technically valid and not very helpful.

Common mistakes

• Limiting rows without sorting first.
• Copying LIMIT into SQL Server, where TOP is commonly used instead.
• Using a small limit in development and forgetting it in a report query.

Related topics

Review SQL ORDER BY, SQL SELECT and the SQL Formatter.