LIMIT and OFFSET

Control the number of results and implement pagination.

12 min read
Beginner

Introduction to LIMIT and OFFSET

Imagine you have a database with 1 million products. When a user searches for "laptop," you get 50,000 results. You don't want to send all 50,000 rows to the user's browser - it would be slow, waste bandwidth, and overwhelm the user!

Instead, you show 10 products per page with "Next" and "Previous" buttons. This is called pagination, and it's powered by LIMIT and OFFSET.

Restricts the number of rows returned by a query. LIMIT 10 means "return at most 10 rows."

Skips a specified number of rows before returning results. OFFSET 20 means "skip the first 20 rows."

Basic Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
LIMIT number_of_rows
OFFSET number_to_skip;

Let's work with our products table (100 products):

products
id
product_name
category
price
rating
1Laptop ProElectronics1299.994.8
2Wireless MouseElectronics29.994.5
3Desk ChairFurniture249.994.7
4Monitor 27"Electronics399.994.6
5Desk LampFurniture49.994.4
...............
100BookshelfFurniture159.994.3

(Showing 5 of 100 products for brevity)

LIMIT - Restricting the Number of Rows

The LIMIT clause controls how many rows are returned. It's useful for:

  • Showing top N results (top 5 best-sellers)
  • Previewing data (show me a sample)
  • Performance (don't load millions of rows)
sql
-- Get only the first 5 products
SELECT product_name, price
FROM products
LIMIT 5;

Only 5 rows returned, even though the table has 100 products.

LIMIT with ORDER BY for meaningful results:

sql
-- Top 5 most expensive products
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;

Now we see the actual top 5 most expensive items! Always use ORDER BY with LIMIT to get consistent, predictable results.

Critical: Without ORDER BY, LIMIT returns arbitrary rows. The database might return different rows each time! Always combine LIMIT with ORDER BY for reliable results.

sql
-- Top 3 highest-rated products
SELECT product_name, rating, price
FROM products
ORDER BY rating DESC, price ASC
LIMIT 3;

We sorted by rating DESC first, then by price ASC as a tiebreaker (when two products have the same rating, show the cheaper one first).

OFFSET - Skipping Rows

OFFSET skips a specified number of rows before returning results. It's rarely used alone but is essential for pagination when combined with LIMIT.

sql
-- Skip the first 10 products, then show the next 5
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5
OFFSET 10;

This skipped the top 10 most expensive products and shows products ranked 11-15.

How it works:

  1. ORDER BY sorts all 100 products by price (descending)
  2. OFFSET 10 skips the first 10 rows (ranks 1-10)
  3. LIMIT 5 returns the next 5 rows (ranks 11-15)

Pagination - LIMIT and OFFSET Together

The real power comes from combining LIMIT and OFFSET to implement pagination - showing results one page at a time.

Pagination Formula:

LIMIT = items_per_page
OFFSET = (page_number - 1) × items_per_page
Pagination Calculation
Page
Items Per Page
OFFSET Calculation
OFFSET Value
LIMIT
110(1 - 1) × 10010
210(2 - 1) × 101010
310(3 - 1) × 102010
410(4 - 1) × 103010

Page 1 - First 10 products:

sql
-- Page 1: Show first 10 products
SELECT product_name, price
FROM products
ORDER BY product_name
LIMIT 10
OFFSET 0;  -- OFFSET 0 can be omitted

Page 2 - Next 10 products:

sql
-- Page 2: Show products 11-20
SELECT product_name, price
FROM products
ORDER BY product_name
LIMIT 10
OFFSET 10;

Page 3 - Products 21-30:

sql
-- Page 3: Show products 21-30
SELECT product_name, price
FROM products
ORDER BY product_name
LIMIT 10
OFFSET 20;

Each page shows exactly 10 products, and they never overlap!

Practical Pagination Example

Let's build a realistic product search with pagination:

sql
-- E-commerce: Electronics category, sorted by rating
-- Page 1, 5 items per page
SELECT product_name, price, rating
FROM products
WHERE category = 'Electronics'
ORDER BY rating DESC, price ASC
LIMIT 5
OFFSET 0;

Page 2 of Electronics (OFFSET 5):

sql
SELECT product_name, price, rating
FROM products
WHERE category = 'Electronics'
ORDER BY rating DESC, price ASC
LIMIT 5
OFFSET 5;

LIMIT Without OFFSET

You can use LIMIT alone when you just want the top N results without pagination:

sql
-- Show top 3 cheapest products
SELECT product_name, price
FROM products
ORDER BY price ASC
LIMIT 3;

OFFSET 0 is the same as no OFFSET:

sql
-- These are identical
SELECT * FROM products ORDER BY price LIMIT 10;
SELECT * FROM products ORDER BY price LIMIT 10 OFFSET 0;

Calculating Total Pages

To build pagination UI, you need to know how many pages there are:

Formula:

total_pages = CEILING(total_rows / items_per_page)

Example: 100 products, 10 per page = 10 pages

  • Page 1: OFFSET 0, LIMIT 10
  • Page 2: OFFSET 10, LIMIT 10
  • ...
  • Page 10: OFFSET 90, LIMIT 10
sql
-- Get total count for pagination UI
SELECT COUNT(*) AS total_products
FROM products
WHERE category = 'Electronics';

45 products ÷ 10 per page = 4.5 → 5 pages (round up)

Database Syntax Differences

Most databases support LIMIT and OFFSET, but syntax varies slightly:

Pagination Syntax Across Databases
Database
Syntax
Example
MySQLLIMIT ... OFFSET ...`LIMIT 10 OFFSET 20`
PostgreSQLLIMIT ... OFFSET ...`LIMIT 10 OFFSET 20`
SQLiteLIMIT ... OFFSET ...`LIMIT 10 OFFSET 20`
SQL ServerOFFSET ... FETCH NEXT`OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY`
OracleOFFSET ... FETCH FIRST`OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY`

MySQL/PostgreSQL shorthand: You can write LIMIT offset, count instead of LIMIT count OFFSET offset. For example, LIMIT 20, 10 means "skip 20, return 10." However, LIMIT count OFFSET offset is clearer and more standard.

Performance Considerations

Large OFFSET values can be slow!

When you use OFFSET 10000, the database still has to:

  1. Process all 10,000 rows
  2. Skip them
  3. Then return the next rows

For deep pagination (page 500+), this gets expensive.

Better approach for deep pagination: Use "keyset pagination" or "cursor-based pagination" instead of OFFSET. Instead of:

LIMIT 10 OFFSET 10000

Use:

WHERE id > last_seen_id
ORDER BY id
LIMIT 10

This is much faster for large datasets!

Common Mistakes to Avoid

Don't make these mistakes:

  1. Using LIMIT without ORDER BY: ❌

    • Results are unpredictable! Different rows each time
    • Always use: ORDER BY column LIMIT 10

  2. Wrong OFFSET calculation: ❌

    • Page 2 should be OFFSET 10 (not OFFSET 2)
    • Formula: (page - 1) × items_per_page

  3. Forgetting OFFSET for page 1: ✓ Actually OK!

    • OFFSET 0 and no OFFSET are the same
    • Page 1 can omit OFFSET

  4. Using LIMIT in COUNT queries: ❌

    • SELECT COUNT(*) FROM products LIMIT 10 is pointless
    • COUNT always returns one row

  5. Negative LIMIT or OFFSET: ❌

    • LIMIT -5 and OFFSET -10 are invalid
    • Must be non-negative integers

  6. LIMIT larger than total rows: ✓ OK!

    • LIMIT 1000 on a 100-row table just returns 100 rows
    • No error, but wasteful

  7. Using OFFSET without LIMIT: Works but unusual

    • OFFSET 50 without LIMIT returns all rows after the first 50
    • Usually you want both

Practical Examples

Example 1: Blog posts (5 per page)

sql
-- Latest blog posts, page 1
SELECT title, author, publish_date
FROM blog_posts
WHERE status = 'published'
ORDER BY publish_date DESC
LIMIT 5
OFFSET 0;

Example 2: Leaderboard (top 10)

sql
-- Top 10 players by score
SELECT username, score, level
FROM players
ORDER BY score DESC, level DESC
LIMIT 10;

Example 3: Search results pagination

sql
-- Search for "desk" in products, page 2 (items 11-20)
SELECT product_name, price, rating
FROM products
WHERE product_name LIKE '%desk%'
ORDER BY rating DESC
LIMIT 10
OFFSET 10;

Key Takeaways

What you learned:

✅ LIMIT restricts the number of rows returned ✅ OFFSET skips a specified number of rows ✅ Pagination formula: OFFSET = (page - 1) × items_per_page ✅ Always use ORDER BY with LIMIT for consistent results ✅ LIMIT without OFFSET returns the first N rows ✅ OFFSET 0 is the same as no OFFSET (page 1) ✅ Large OFFSET values can hurt performance ✅ Different databases have slightly different syntax

Coming up next: We'll learn about aggregate functions (COUNT, SUM, AVG, MIN, MAX) to calculate statistics from your data!

Practice Exercise: Try these queries:

  1. Get the top 5 cheapest products
  2. Get products 21-30 when sorted by name
  3. Implement page 4 of a product list (10 items per page)
  4. Get the 3 oldest employees (by hire_date)
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.