SQL TOP, LIMIT and FETCH FIRST: Limit Rows Returned

SQL Tutorial · Chapter 8 of 32

SQL LIMIT, TOP 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 MS Access, and FETCH FIRST by Oracle, DB2, PostgreSQL, SQL Server 2012+ and the SQL standard. Combined with ORDER BY, they give you top-N lists, “latest record” queries and paginated results.

This chapter of our SQL from Scratch series is for beginners and analysts who already know SELECT and ORDER BY. You will see the syntax for each database, worked examples on one small Orders table with the exact results, pagination with OFFSET, top-N per group, and the mistakes that cause wrong or unstable answers.

SQL LIMIT, TOP and FETCH FIRST Syntax

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

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

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

Which keyword does your database use?

Arguments explained

Sample Table

Example 1: Top 3 Orders by Amount (LIMIT)

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

The database sorts all six orders by Amount from highest to lowest, then keeps only the first three.

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 sits right after SELECT (and after DISTINCT if you use it), not at the end of the query.

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 and DB2. SQL Server also supports it but requires an OFFSET clause first, 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. The SQL Server and Oracle equivalent is:

SELECT OrderID, OrderDate
FROM Orders
ORDER BY OrderDate
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.

More Practical Variations

Include ties with WITH TIES

Suppose two orders shared the third-highest amount. LIMIT 3 would drop one of them at random. In SQL Server, Oracle and PostgreSQL 13+ you can keep both:

-- SQL Server
SELECT TOP 3 WITH TIES OrderID, Amount
FROM Orders
ORDER BY Amount DESC;

-- Oracle / PostgreSQL 13+
SELECT OrderID, Amount
FROM Orders
ORDER BY Amount DESC
FETCH FIRST 3 ROWS WITH TIES;

On our sample table every amount is different, so the result is still the same three rows.

Return a percentage of rows (SQL Server)

SELECT TOP 50 PERCENT OrderID, Amount
FROM Orders
ORDER BY Amount DESC;

Half of six rows is three, so this returns orders 101, 106 and 103. PERCENT is SQL Server only.

Most recent order

SELECT OrderID, Product, OrderDate
FROM Orders
ORDER BY OrderDate DESC
LIMIT 1;
-- 106, Laptop, 2026-03-01

Top order per customer (top-N per group)

LIMIT applies to the whole result, not to each group. To get the biggest order for each customer, number the rows inside each group with a window function and filter:

SELECT OrderID, CustomerID, Amount
FROM (
  SELECT OrderID, CustomerID, Amount,
         ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Amount DESC) AS rn
  FROM Orders
) AS ranked
WHERE rn = 1;

Customer 1 has two orders (1200 and 300), so only order 101 is kept for that customer; every other customer has a single order and appears once. This works in MySQL 8+, PostgreSQL, SQLite 3.25+, SQL Server and Oracle.

Faster deep pagination (keyset pagination)

Large OFFSET values get slow because the database still reads and throws away all skipped rows. For “next page” buttons on big tables, remember the last value you showed and continue from there:

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderID > 104
ORDER BY OrderID
LIMIT 2;
-- 105, 106

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.

Database Differences to Remember

• MySQL also accepts the shorthand LIMIT offset, count (LIMIT 2, 2). SQLite accepts it too; PostgreSQL does not. Note the order: offset first, count second.
• SQL Server has no LIMIT; use TOP or OFFSET/FETCH. OFFSET/FETCH requires ORDER BY.
• Older Oracle versions (before 12c) have no FETCH FIRST and use WHERE ROWNUM <= n on an ordered subquery.
• TOP (n) PERCENT is SQL Server only; WITH TIES is supported by SQL Server, Oracle and PostgreSQL 13+, but not MySQL or SQLite.
• TOP accepts a variable in parentheses: SELECT TOP (@n) ....

Common Errors and Fixes

Tips for Using LIMIT Well

• Index the ORDER BY column on large tables so the database can stop early instead of sorting everything.
• Use LIMIT 10 when exploring an unfamiliar table to avoid pulling millions of rows.
• Prefer keyset pagination over large OFFSET values for APIs and infinite scroll.
• Combine with WHERE to filter first, then limit, for example the top 5 orders of 2026 only.

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 SELECT DISTINCT – unique values before limiting
• SQL from Scratch: full course overview

Many analysts take the result of a top-N query into Excel for reporting. If that is your workflow, the Pivot Tables and Dashboards course on NextGenTemplates Academy shows how to turn exported query results into summaries and dashboards.

Frequently Asked Questions

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 and MS Access.

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.

Is FETCH FIRST the same as LIMIT?

Yes in effect. FETCH FIRST n ROWS ONLY is the SQL-standard spelling; LIMIT is the older, shorter form supported by MySQL, PostgreSQL and SQLite.

How do I get the top 3 rows for each group?

Use ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC) in a subquery and keep rows where the number is 3 or less. LIMIT on its own applies to the whole result, not to each group.

Can I use LIMIT without ORDER BY?

You can, but the rows returned are not guaranteed and may change between runs. Use it only for quick previews of a table.

Wrapping Up

LIMIT, TOP and FETCH FIRST all answer the same question: how many rows should come back. Pick the keyword your database supports, always sort with ORDER BY first, add a unique tie-breaker for pagination, and switch to ROW_NUMBER when you need the top rows per group. Next, try the practice exercise above, then continue to SQL MIN and MAX.

📅 Last updated: October 2026

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