SQL LIMIT and OFFSET to Limit Query Results

Up to this point, every query you have written returns all matching rows. If your table has 10 products, you get 10 rows back. But what happens when your table has 10,000 products, or a million orders? Retrieving everything is slow, wasteful, and rarely what your application actually needs.

In practice, you almost always want a subset of results. The top 5 best sellers. The 10 most recent orders. Page 3 of search results showing items 21 through 30. This is where LIMIT and OFFSET come in.

LIMIT controls how many rows the database returns. OFFSET tells the database how many rows to skip before starting to return results. Together, they power some of the most common features in modern applications: top-N lists, pagination, dashboards, and preview panels.

This guide covers LIMIT and OFFSET in depth, explains the syntax differences across database systems (LIMIT vs TOP vs FETCH FIRST), walks through real-world pagination, and demonstrates practical use cases with the ShopSmart sample database (we defined it in a previous guide here).

How LIMIT Works

The LIMIT clause restricts the maximum number of rows returned by a query. It goes at the very end of your statement, after ORDER BY:

SELECT columns
FROM table
WHERE condition       -- optional
ORDER BY column       -- optional but recommended
LIMIT number;

Here is the simplest example. Retrieve only 3 products:

SELECT name, price
FROM products
LIMIT 3;

Output:

The products table has 10 rows, but the query returns only 3. The database stops after finding the first 3 rows that match.

LIMIT with ORDER BY: The Core Pattern

The most common and most useful pattern is ORDER BY combined with LIMIT. This answers questions like "show me the top N" or "show me the bottom N":

The 5 Most Expensive Products

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

Output:

Sort by price descending (highest first), then take only the first 5 rows. The result is the top 5 most expensive products.

The 3 Cheapest Available Products

SELECT name, price
FROM products
WHERE is_available = true
ORDER BY price ASC
LIMIT 3;

Output:

The WHERE clause filters to available products, ORDER BY sorts by price ascending (cheapest first), and LIMIT takes only the first 3.

The Most Recent Order

SELECT id, order_date, total_amount, status
FROM orders
ORDER BY order_date DESC
LIMIT 1;

Output:

LIMIT 1 returns exactly one row, which is the single most recent order.

The 3 Oldest Customer Accounts

SELECT first_name, last_name, signup_date
FROM customers
ORDER BY signup_date ASC
LIMIT 3;

Output:

How OFFSET Works

OFFSET tells the database to skip a specified number of rows before starting to return results. It is almost always used together with LIMIT.

SELECT columns
FROM table
ORDER BY column
LIMIT number
OFFSET skip_count;

Here is a simple demonstration. Get 3 products, but skip the first 2:

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 3
OFFSET 2;

Output:

The database sorted all products by price (ascending), skipped the first 2 rows (Stainless Water Bottle at $24.99 and Wireless Mouse at $29.99), and then returned the next 3 rows.

Visualizing OFFSET

Consider all products sorted by price ascending:

With LIMIT 3 OFFSET 0, you get rows 1, 2, 3. With LIMIT 3 OFFSET 2, you get rows 3, 4, 5. With LIMIT 3 OFFSET 5, you get rows 6, 7, 8.

Pagination: The Real-World Use Case

The most important practical application of LIMIT and OFFSET is pagination, the process of dividing a large result set into smaller "pages" that users can navigate through.

Think of any e-commerce website, search engine, or social media feed. They do not load all 50,000 products or all 10 million posts at once. They show you one page at a time (typically 10, 20, or 25 items) and let you click "Next" to see more.

Building Pagination Step by Step

Imagine your product listing page shows 3 products per page (we use 3 for simplicity; real applications typically use 10, 20, or 25).

Page 1 (first 3 products):

SELECT name, price
FROM products
ORDER BY name ASC
LIMIT 3
OFFSET 0;

Output:

Page 2 (skip the first 3, show the next 3):

SELECT name, price
FROM products
ORDER BY name ASC
LIMIT 3
OFFSET 3;

Output:

Page 3 (skip the first 6, show the next 3):

SELECT name, price
FROM products
ORDER BY name ASC
LIMIT 3
OFFSET 6;

Output:

Page 4 (skip the first 9, show the next 3):

SELECT name, price
FROM products
ORDER BY name ASC
LIMIT 3
OFFSET 9;

Output:

The last page has only 1 product because there are 10 products total. The database returns whatever remains after the offset, even if it is fewer rows than the limit.

The Pagination Formula

The pattern for calculating the OFFSET for any page is:

OFFSET = (page_number - 1) × items_per_page

In application code, this typically looks something like:

-- Generic pagination query
SELECT name, price
FROM products
ORDER BY name ASC
LIMIT 10
OFFSET 20;  -- This would be page 3 with 10 items per page

Syntax Across Different Databases

One of the notable differences between SQL dialects is how they express result limiting. The concept is the same everywhere, but the syntax varies.

MySQL and PostgreSQL: LIMIT / OFFSET

MySQL and PostgreSQL both use the LIMIT and OFFSET keywords:

-- PostgreSQL and MySQL: identical syntax
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5
OFFSET 10;

MySQL also supports an alternative comma syntax where OFFSET comes first:

-- MySQL alternative syntax: LIMIT offset, count
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 10, 5;  -- Skip 10, return 5

SQLite: LIMIT / OFFSET

SQLite uses the same syntax as MySQL and PostgreSQL:

-- SQLite: same as MySQL/PostgreSQL
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5
OFFSET 10;

SQL Server: TOP and OFFSET/FETCH

SQL Server has its own approach. For simple "top N" queries, it uses the TOP keyword placed after SELECT:

-- SQL Server: TOP for simple limiting
SELECT TOP 5 name, price
FROM products
ORDER BY price DESC;

For pagination (with an offset), SQL Server uses the OFFSET...FETCH syntax, which is actually part of the ANSI SQL standard:

-- SQL Server: OFFSET/FETCH for pagination
SELECT name, price
FROM products
ORDER BY price DESC
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

ANSI SQL Standard: FETCH FIRST

The official ANSI SQL standard defines the FETCH FIRST syntax, which works in PostgreSQL, SQL Server, Oracle, and DB2:

-- ANSI SQL standard syntax
SELECT name, price
FROM products
ORDER BY price DESC
FETCH FIRST 5 ROWS ONLY;

-- With OFFSET (ANSI standard)
SELECT name, price
FROM products
ORDER BY price DESC
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

Quick Reference: Syntax Comparison

Practical Use Case: "Show the 10 Most Expensive Products"

Let us walk through a complete real-world scenario. You are building a product showcase section for an e-commerce homepage that highlights the store's premium products.

Step 1: Get All Products Sorted by Price

First, see the full sorted list to understand your data:

SELECT name, price, category_id, is_available
FROM products
ORDER BY price DESC;

Output:

Step 2: Limit to Top 5

Now take only the 5 most expensive:

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

Output:

Step 3: Refine with Business Logic

The showcase should only display products that are actually available for purchase. The Bluetooth Speaker is out of stock, so let us filter it out:

SELECT name, price
FROM products
WHERE is_available = true
ORDER BY price DESC
LIMIT 5;

Output:

Now the Bluetooth Speaker is gone, and the Data Science Handbook has moved up to fill the 5th spot. This is the query your application would use to populate the "Premium Products" section.

Step 4: Add Category Context

For an even richer display, join with the categories table:

SELECT p.name, p.price, c.name AS category
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.is_available = true
ORDER BY p.price DESC
LIMIT 5;

Output:

This is a production-ready query that could power a real product showcase widget.

More Practical Patterns

The Single Newest Record

A very common pattern is finding the single most recent entry in a table:

-- The most recent order
SELECT id, order_date, total_amount, status
FROM orders
ORDER BY order_date DESC
LIMIT 1;

Output:

The Largest Single Order

-- The order with the highest total
SELECT id, order_date, total_amount, status
FROM orders
ORDER BY total_amount DESC
LIMIT 1;

Output:

Top 3 Customers by Signup Date (Newest First)

SELECT first_name, last_name, signup_date
FROM customers
ORDER BY signup_date DESC
LIMIT 3;

Output:

Bottom 3 Products by Stock

-- Products running lowest on stock
SELECT name, stock_quantity, price
FROM products
WHERE is_available = true
ORDER BY stock_quantity ASC
LIMIT 3;

Output:

This is the kind of query an inventory management system would use to generate a "low stock alert" report.

Sample a Few Rows from a Large Table

Sometimes you just want to peek at a few rows to understand a table's contents:

-- Quick peek at the order_items table
SELECT *
FROM order_items
LIMIT 5;

Output:

This is one of the few cases where LIMIT without ORDER BY is acceptable since you are just exploring, not building a feature.

OFFSET Edge Cases

Understanding a few edge cases will help you avoid confusion with OFFSET.

OFFSET Beyond Available Rows

If OFFSET exceeds the total number of rows, the query returns an empty result set, not an error:

-- Only 10 products exist, but we skip 50
SELECT name, price
FROM products
ORDER BY price
LIMIT 5
OFFSET 50;

Output:

(empty result set, zero rows)

This is normal and expected behavior. Your application should handle this gracefully, typically by displaying a "No more results" message.

OFFSET 0

OFFSET 0 is the same as no offset at all. It skips zero rows:

-- These two queries are identical
SELECT name, price FROM products ORDER BY price LIMIT 5;
SELECT name, price FROM products ORDER BY price LIMIT 5 OFFSET 0;

LIMIT Without OFFSET

When you use LIMIT without OFFSET, the offset defaults to 0:

-- These are equivalent
SELECT name FROM products LIMIT 5;
SELECT name FROM products LIMIT 5 OFFSET 0;

OFFSET Without LIMIT

In PostgreSQL and SQLite, you can use OFFSET without LIMIT, which skips rows but returns everything after that:

-- PostgreSQL/SQLite: Skip the first 7 products, return the rest
SELECT name, price
FROM products
ORDER BY price ASC
OFFSET 7;

Output:

In MySQL, OFFSET without LIMIT is not allowed. You would need to provide an arbitrarily large LIMIT value:

-- MySQL: OFFSET requires LIMIT, so use a very large number
SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 999999999
OFFSET 7;

Combining LIMIT with Everything You Have Learned

Here are a few comprehensive examples that combine LIMIT and OFFSET with WHERE, AND/OR, and ORDER BY:

The 3 Most Expensive Available Electronics

SELECT name, price
FROM products
WHERE category_id = 1
  AND is_available = true
ORDER BY price DESC
LIMIT 3;

Output:

The 2 Most Recent Completed Orders Over $80

SELECT id, order_date, total_amount
FROM orders
WHERE status = 'completed'
  AND total_amount > 80
ORDER BY order_date DESC
LIMIT 2;

Output:

Paginated Order History (Page 2, 3 Items Per Page)

SELECT id, order_date, total_amount, status
FROM orders
ORDER BY order_date DESC
LIMIT 3
OFFSET 3;

Output:

Practical Exercises

Exercise 1

Find the single cheapest product in the store.

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 1;

Expected output:

Exercise 2

Find the 3 most recent orders that are still pending.

SELECT id, order_date, total_amount
FROM orders
WHERE status = 'pending'
ORDER BY order_date DESC
LIMIT 3;

Expected output:

Exercise 3

Display page 2 of the product list (sorted by name, 4 items per page).

SELECT name, price
FROM products
ORDER BY name ASC
LIMIT 4
OFFSET 4;

Expected output:

Exercise 4

Find the 2 customers who have been members the longest.

SELECT first_name, last_name, signup_date
FROM customers
ORDER BY signup_date ASC
LIMIT 2;

Expected output:

Exercise 5

Find the top 3 highest-value orders that are either shipped or completed.

SELECT id, order_date, total_amount, status
FROM orders
WHERE status = 'shipped' OR status = 'completed'
ORDER BY total_amount DESC
LIMIT 3;

Expected output:

Key Takeaways

LIMIT and OFFSET give you precise control over how many rows your query returns and which portion of the results you see. Here is what you should remember:

• LIMIT restricts the maximum number of rows returned
• OFFSET skips a specified number of rows before returning results
• Always use ORDER BY with LIMIT to ensure predictable, meaningful results
• The clause order is: SELECT → FROM → WHERE → ORDER BY → LIMIT → OFFSET
• Pagination formula: OFFSET = (page_number - 1) × items_per_page
• Different databases use different syntax: LIMIT (MySQL, PostgreSQL, SQLite), TOP (SQL Server), and FETCH FIRST (ANSI standard)
• OFFSET beyond available rows returns an empty result set, not an error
• OFFSET pagination becomes slow on large datasets because the database must scan and discard all skipped rows
• The LIMIT 1 pattern combined with ORDER BY is the standard way to find the single "most" or "least" of anything
• LIMIT without ORDER BY is only appropriate for quick data exploration, never for application features

With SELECT, FROM, WHERE, logical operators, ORDER BY, LIMIT, and OFFSET, you now have a complete toolkit for writing targeted, sorted, paginated queries. These clauses form the backbone of the vast majority of SQL queries in production applications.