SQL TOP, LIMIT and FETCH FIRST: Limit Rows Returned

SQL Tutorial · Chapter 8 of 32

SQL TOP, LIMIT and FETCH FIRST are three ways of doing the same job: restricting how many rows a query returns. LIMIT is used by MySQL, PostgreSQL and SQLite, TOP by SQL Server, and FETCH FIRST by Oracle, PostgreSQL, SQL Server and the SQL standard. Combined with ORDER BY they give you top-N lists and paginated results.

Syntax in Each Database

-- MySQL, PostgreSQL, SQLite
SELECT columns FROM table_name ORDER BY column LIMIT n;

-- SQL Server
SELECT TOP n columns FROM table_name ORDER BY column;

-- Oracle, PostgreSQL, SQL Server 2012+, standard SQL
SELECT columns FROM table_name ORDER BY column
OFFSET m ROWS FETCH FIRST n ROWS ONLY;

Sample Table

Example 1: Top 3 Orders by Amount (LIMIT)

SELECT OrderID, Product, Amount
FROM Orders
ORDER BY Amount DESC
LIMIT 3;

Example 2: The Same Query in SQL Server (TOP)

SELECT TOP 3 OrderID, Product, Amount
FROM Orders
ORDER BY Amount DESC;

Result: identical to Example 1. TOP 3 WITH TIES would also include any further rows tied with the third value.

Example 3: Standard FETCH FIRST

SELECT OrderID, Product, Amount
FROM Orders
ORDER BY Amount DESC
FETCH FIRST 3 ROWS ONLY;

Result: identical to Example 1. This form works in Oracle 12c+, PostgreSQL, DB2 and SQL Server 2012+ (SQL Server requires an OFFSET clause before it, even OFFSET 0 ROWS).

Example 4: Pagination with OFFSET

OFFSET skips rows before counting. Page 2 with two rows per page skips the first two rows.

SELECT OrderID, OrderDate
FROM Orders
ORDER BY OrderDate
LIMIT 2 OFFSET 2;

The general formula is LIMIT page_size OFFSET (page_number - 1) * page_size. In SQL Server and Oracle write OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY.

Example 5: The Single Cheapest Order

SELECT OrderID, Product, Amount
FROM Orders
ORDER BY Amount ASC
LIMIT 1;

This is often simpler than a MIN subquery when you need the whole row, not just the minimum value.

Why ORDER BY Matters

Without ORDER BY, LIMIT returns an arbitrary set of rows: whichever the database reaches first. That may look stable on a small table, then change after an index is added or data grows. Treat LIMIT without ORDER BY as a quick preview only, never as a business answer.

Works in MySQL, SQL Server, PostgreSQL, SQLite

• MySQL also accepts the shorthand LIMIT offset, count (LIMIT 2, 2). SQLite accepts it too; PostgreSQL does not.
• SQL Server has no LIMIT; use TOP or OFFSET/FETCH. Older Oracle versions use ROWNUM.
• TOP (n) PERCENT is SQL Server only.

Common Mistakes

• Using LIMIT in SQL Server or TOP in MySQL; each raises a syntax error.
• Omitting ORDER BY and assuming the “top” rows are meaningful.
• Paginating without a unique tie-breaker column, so rows repeat or vanish between pages. Add the primary key as the last ORDER BY column.

Practice Exercise

1. Return the two most recent orders (OrderID, OrderDate).
2. Return the third and fourth cheapest orders using OFFSET.

-- 1
SELECT OrderID, OrderDate FROM Orders ORDER BY OrderDate DESC LIMIT 2;
-- 106 (2026-03-01), 105 (2026-02-15)

-- 2
SELECT OrderID, Amount FROM Orders ORDER BY Amount ASC LIMIT 2 OFFSET 2;
-- 105 (80), 103 (300)

Related Chapters

• SQL ORDER BY – always pair it with LIMIT
• SQL MIN and MAX – another way to find extremes
• SQL Window Functions – top-N per group with ROW_NUMBER
• SQL from Scratch: full course overview

FAQ

What is the difference between LIMIT and TOP?

They do the same thing in different databases. LIMIT goes at the end of a query in MySQL, PostgreSQL and SQLite; TOP goes straight after SELECT in SQL Server.

How do I get rows 11 to 20 in SQL?

Use ORDER BY with an offset: LIMIT 10 OFFSET 10 in MySQL, PostgreSQL and SQLite, or OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY in SQL Server and Oracle.

Does LIMIT make queries faster?

Often yes, because the database can stop once it has enough rows. If the query sorts an unindexed column it must still sort everything first, so add an index on the ORDER BY column for large tables.

Chapter 8 of 32 · SQL from Scratch: all 32 chapters