LIMIT, TOP, and FETCH NEXT in SQL

Why You Need Row Limiting

You are exploring a new table and want a quick look at the data without pulling a million rows into your report tool. Or your manager wants a "top 10 customers by spend" list. Without a way to cap results, every query returns everything, which is slow to run and hard to read. Row limiting lets you get exactly the slice of data you need.

LIMIT in SQLite

Since the examples in this lesson use SQLite, LIMIT is the syntax you will use. Place it at the very end of your query.

-- First 10 products in the table
SELECT product_id, product_name, price
FROM products p
LIMIT 10;

-- 5 most recent orders
SELECT order_id, customer_id, order_date, total_amount
FROM orders o
ORDER BY order_date DESC
LIMIT 5;

TOP in SQL Server

If you work in SQL Server, the syntax is TOP n placed between SELECT and your column list. The logic is identical to LIMIT.

-- SQL Server syntax shown for reference, not valid in SQLite
-- SELECT TOP 10 product_id, product_name, price
-- FROM products p
-- ORDER BY price DESC;

This lesson uses SQLite throughout, but knowing TOP exists saves you when you switch environments.

FETCH NEXT in Standard SQL

FETCH NEXT is the ISO standard syntax supported by Oracle, PostgreSQL, and SQL Server 2012 and later. It is more verbose but reads clearly.

-- Standard SQL syntax shown for reference, not valid in SQLite
-- SELECT product_id, product_name, price
-- FROM products p
-- ORDER BY price DESC
-- OFFSET 0 ROWS
-- FETCH NEXT 10 ROWS ONLY;

The structure is always: ORDER BY, then OFFSET, then FETCH NEXT. Again, this lesson uses SQLite so you will use LIMIT in all exercises.

Combining LIMIT with ORDER BY

This is the most common real-world pattern. Sort your data to define what "top" or "bottom" means, then cut it off with LIMIT.

-- Top 5 most expensive active products
SELECT product_id, product_name, brand, price
FROM products p
WHERE is_active = 1
ORDER BY price DESC
LIMIT 5;

-- 3 customers who signed up most recently
SELECT customer_id, first_name, last_name, email, created_at
FROM customers c
ORDER BY created_at DESC
LIMIT 3;

-- Lowest 10 stock items that are still active
SELECT product_id, product_name, stock_qty
FROM products p
WHERE is_active = 1
ORDER BY stock_qty ASC
LIMIT 10;

OFFSET: Skipping Rows for Pagination

OFFSET tells SQL how many rows to skip before it starts returning results. Pair it with LIMIT to page through data.

-- Skip the first 10 orders, return the next 10 (page 2)
SELECT order_id, customer_id, order_date, total_amount
FROM orders o
ORDER BY order_date DESC
LIMIT 10 OFFSET 10;

-- Page 3 of products sorted by name, 5 per page
SELECT product_id, product_name, price
FROM products p
ORDER BY product_name ASC
LIMIT 5 OFFSET 10;

The formula for any page is: OFFSET = (page_number - 1) * page_size. Page 1 is OFFSET 0, page 2 is OFFSET 10 if your page size is 10, and so on.

Common Mistakes

-- wrong
SELECT order_id, customer_id, total_amount
FROM orders o
LIMIT 5
ORDER BY total_amount DESC;

LIMIT must come after ORDER BY, not before it. This is a syntax error in SQLite.

-- correct
SELECT order_id, customer_id, total_amount
FROM orders o
ORDER BY total_amount DESC
LIMIT 5;

---

-- wrong
SELECT product_id, product_name, price
FROM products p
LIMIT 10;

Without ORDER BY, there is no guarantee which 10 rows you get back. Two runs of the same query can return different rows.

-- correct
SELECT product_id, product_name, price
FROM products p
ORDER BY price DESC
LIMIT 10;

---

-- wrong
SELECT order_id, customer_id, order_date
FROM orders o
ORDER BY order_date DESC
LIMIT 10 OFFSET -1;

OFFSET must be zero or a positive integer. A negative offset causes an error in SQLite.

-- correct
SELECT order_id, customer_id, order_date
FROM orders o
ORDER BY order_date DESC
LIMIT 10 OFFSET 0;

Quick Recap

• Use LIMIT n in SQLite to cap the number of rows returned
• TOP n is the SQL Server equivalent and goes right after SELECT
• FETCH NEXT n ROWS ONLY is the ISO standard syntax used in Oracle and others
• Always combine LIMIT with ORDER BY so you control which rows you get
• Use LIMIT n OFFSET m to skip rows and implement pagination

SELECT employee_id, first_name, last_name, role, hire_date FROM employees e ORDER BY hire_date DESC LIMIT 5;

SELECT order_id, customer_id, order_date, total_amount FROM orders o WHERE status = 'delivered' ORDER BY total_amount DESC LIMIT 10; SELECT order_id, customer_id, order_date, total_amount FROM orders o WHERE status = 'delivered' ORDER BY total_amount DESC LIMIT 10 OFFSET 10;

SELECT c.customer_id, c.first_name, c.last_name, SUM(o.total_amount) AS total_spend FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.first_name, c.last_name ORDER BY total_spend DESC LIMIT 5;