Course contentsShow
← Data Science
Lesson 878 of 2,14522. SQL FoundationsFree lesson

OFFSET: Skipping Rows for Pagination

Combine LIMIT with OFFSET to implement pagination and retrieve specific subsets of ordered results.

OFFSET: Skipping Rows for Pagination

What you'll learn: How to skip a specific number of rows in your query results to build pagination systems.

The Core Idea

Think of OFFSET like flipping through pages in a book. If you want to read page 3, you skip pages 1 and 2 first. In SQL, OFFSET tells the database how many rows to skip before starting to return results. When combined with LIMIT, you can slice your data into chunks—perfect for displaying results across multiple pages in an app or dashboard.

How OFFSET Works

The syntax is straightforward:

SELECT column1, column2
FROM table_name
ORDER BY column1
LIMIT 10 OFFSET 20;

This query skips the first 20 rows and then returns the next 10 rows (rows 21-30).

Building Pagination

Imagine you're showing customer records, 25 per page:

  • Page 1: LIMIT 25 OFFSET 0 (rows 1-25)
  • Page 2: LIMIT 25 OFFSET 25 (rows 26-50)
  • Page 3: LIMIT 25 OFFSET 50 (rows 51-75)

The formula is: OFFSET = (page_number - 1) × page_size

Why ORDER BY Matters

Always use ORDER BY with OFFSET. Without consistent ordering, the database might return different rows each time, making pagination unreliable. You need a stable sort order to guarantee users see logical, sequential pages.

SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 40;

This reliably returns products 41-60 when sorted by ID.

Key Takeaway: OFFSET skips a specified number of rows before returning results—combine it with LIMIT and ORDER BY to implement consistent, reliable pagination in your queries.