Skip to main content

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:

nameprice
Wireless Mouse29.99
Mechanical Keyboard89.99
USB-C Hub45.00

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

LIMIT Without ORDER BY Is Unpredictable

In the example above, the 3 rows returned are not necessarily the "first" 3 in any meaningful sense. Without ORDER BY, the database returns rows in whatever internal order it finds them. That order can change over time.

-- Unpredictable: Which 3 products will you get?
SELECT name, price
FROM products
LIMIT 3;

-- Predictable: The 3 cheapest products, guaranteed
SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 3;

Always combine LIMIT with ORDER BY unless the specific rows returned truly do not matter.

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:

nameprice
Coffee Maker Pro129.99
Running Shoes X1110.00
Mechanical Keyboard89.99
Bluetooth Speaker65.00
USB-C Hub45.00

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:

nameprice
Stainless Water Bottle24.99
Wireless Mouse29.99
SQL for Beginners34.99

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:

idorder_datetotal_amountstatus
82024-04-10199.98pending

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:

first_namelast_namesignup_date
AliceJohnson2023-01-15
BobMartinez2023-03-22
CarolSingh2023-06-10
Think of LIMIT as a Cutoff

Imagine you have a sorted list of 100 items written on paper. LIMIT 5 means you tear off the paper after the 5th item and throw away the rest. You still get them in the sorted order, but you only get the first 5.

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:

nameprice
SQL for Beginners34.99
Yoga Mat Premium38.00
Data Science Handbook42.50

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:

Row #namepriceOFFSET 0OFFSET 2OFFSET 5
1Stainless Water Bottle24.99✅skippedskipped
2Wireless Mouse29.99✅skippedskipped
3SQL for Beginners34.99✅✅skipped
4Yoga Mat Premium38.00✅skipped
5Data Science Handbook42.50✅skipped
6USB-C Hub45.00✅
7Bluetooth Speaker65.00✅
8Mechanical Keyboard89.99✅
9Running Shoes X1110.00
10Coffee Maker Pro129.99

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:

nameprice
Bluetooth Speaker65.00
Coffee Maker Pro129.99
Data Science Handbook42.50

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

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

Output:

nameprice
Mechanical Keyboard89.99
Running Shoes X1110.00
SQL for Beginners34.99

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

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

Output:

nameprice
Stainless Water Bottle24.99
USB-C Hub45.00
Wireless Mouse29.99

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

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

Output:

nameprice
Yoga Mat Premium38.00

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
PageItems Per PageOFFSET CalculationOFFSET Value
110(1 - 1) × 100
210(2 - 1) × 1010
310(3 - 1) × 1020
410(4 - 1) × 1030
510(5 - 1) × 1040

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
OFFSET Performance on Large Datasets

While LIMIT/OFFSET pagination is simple and intuitive, it has a significant performance problem with large datasets. When you write OFFSET 100000, the database must internally scan and discard 100,000 rows before returning your results. The higher the offset, the slower the query.

-- Fast: Skipping 0 rows
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;

-- Slower: The database must scan through 10,000 rows and discard them
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10000;

-- Much slower: Scanning and discarding 1,000,000 rows
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 1000000;

For small to medium datasets (tens of thousands of rows), OFFSET pagination works perfectly fine. For very large datasets (millions of rows), more advanced techniques like keyset pagination (also called "cursor-based pagination") are preferred. That is a topic for a more advanced guide.

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
The MySQL Comma Syntax Is Confusing

The MySQL comma syntax puts the offset before the limit, which is the reverse of the LIMIT...OFFSET order. This easily leads to mistakes.

-- MySQL comma syntax: LIMIT offset, count
LIMIT 10, 5        -- Skip 10 rows, then return 5 rows

-- LIMIT...OFFSET syntax: LIMIT count OFFSET skip
LIMIT 5 OFFSET 10  -- Return 5 rows, skipping the first 10

-- Both produce the same result, but the comma version is easy to misread

Prefer the LIMIT...OFFSET syntax for clarity. It works in both MySQL and PostgreSQL and reads more naturally.

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;
Important

In SQL Server, OFFSET...FETCH requires an ORDER BY clause. You cannot use it without specifying a sort order. This is actually a good thing because, as discussed earlier, using LIMIT or OFFSET without ORDER BY gives unpredictable results anyway.

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​

DatabaseTop 5 ProductsSkip 10, Get 5
MySQLLIMIT 5LIMIT 5 OFFSET 10
PostgreSQLLIMIT 5LIMIT 5 OFFSET 10
SQLiteLIMIT 5LIMIT 5 OFFSET 10
SQL ServerSELECT TOP 5 ...OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
ANSI SQLFETCH FIRST 5 ROWS ONLYOFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
Which Syntax Should You Use?

If you are working with MySQL, PostgreSQL, or SQLite, use LIMIT/OFFSET. It is clean, concise, and universally understood in these systems. If you are working with SQL Server, use TOP for simple queries and OFFSET...FETCH for pagination. If you want maximum portability, the OFFSET...FETCH syntax is the closest to the ANSI standard and works in PostgreSQL, SQL Server, and Oracle.

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:

namepricecategory_idis_available
Coffee Maker Pro129.993true
Running Shoes X1110.004true
Mechanical Keyboard89.991true
Bluetooth Speaker65.001false
USB-C Hub45.001true
Data Science Handbook42.502true
Yoga Mat Premium38.004true
SQL for Beginners34.992true
Wireless Mouse29.991true
Stainless Water Bottle24.994true

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:

nameprice
Coffee Maker Pro129.99
Running Shoes X1110.00
Mechanical Keyboard89.99
Bluetooth Speaker65.00
USB-C Hub45.00

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:

nameprice
Coffee Maker Pro129.99
Running Shoes X1110.00
Mechanical Keyboard89.99
USB-C Hub45.00
Data Science Handbook42.50

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:

namepricecategory
Coffee Maker Pro129.99Home & Kitchen
Running Shoes X1110.00Sports
Mechanical Keyboard89.99Electronics
USB-C Hub45.00Electronics
Data Science Handbook42.50Books

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:

idorder_datetotal_amountstatus
82024-04-10199.98pending

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:

idorder_datetotal_amountstatus
82024-04-10199.98pending

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:

first_namelast_namesignup_date
FrankWilson2024-02-28
EvaBrown2024-01-18
DavidChen2023-08-05

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:

namestock_quantityprice
Coffee Maker Pro25129.99
Data Science Handbook3042.50
SQL for Beginners5034.99

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:

idorder_idproduct_idquantityunit_price
111229.99
213145.00
322189.99
434134.99
5310124.99

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:

nameprice
Mechanical Keyboard89.99
Running Shoes X1110.00
Coffee Maker Pro129.99

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:

nameprice
Mechanical Keyboard89.99
USB-C Hub45.00
Wireless Mouse29.99

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:

idorder_datetotal_amount
22024-01-1589.99
12024-01-10119.98

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:

idorder_datetotal_amountstatus
52024-03-12155.00shipped
42024-03-05129.99shipped
32024-02-2077.49completed

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:

nameprice
Stainless Water Bottle24.99

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:

idorder_datetotal_amount
82024-04-10199.98
72024-04-0163.00
62024-03-2034.99

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:

nameprice
Running Shoes X1110.00
SQL for Beginners34.99
Stainless Water Bottle24.99
USB-C Hub45.00

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:

first_namelast_namesignup_date
AliceJohnson2023-01-15
BobMartinez2023-03-22

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:

idorder_datetotal_amountstatus
52024-03-12155.00shipped
42024-03-05129.99shipped
12024-01-10119.98completed

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.

Table of Contents