OFFSET for pagination in SQL

Introduction

OFFSET helps you skip a certain number of rows in a list, so you can see the next set of results, like turning pages in a book.

When showing search results page by page on a website.

When displaying a list of items in chunks instead of all at once.

When you want to load more data as the user scrolls down.

When you need to jump to a specific page of results in a report.

Syntax

SQL

SELECT column1, column2 FROM table_name ORDER BY column1 OFFSET number_of_rows_to_skip ROWS;

OFFSET must be used with ORDER BY to ensure consistent results.

OFFSET skips the first N rows and returns the rest.

Examples

Skip the first 5 employees and show the rest ordered by name.

SQL

SELECT name FROM employees ORDER BY name OFFSET 5 ROWS;

Skip the first 10 books and show the rest ordered by id.

SQL

SELECT id, title FROM books ORDER BY id OFFSET 10 ROWS;

Skip the first 20 products sorted by price.

SQL

SELECT * FROM products ORDER BY price OFFSET 20 ROWS;

Sample Program

This creates a table of fruits, inserts 7 fruits, then selects all fruits skipping the first 3 by id order.

SQL

CREATE TABLE fruits (id INT, name VARCHAR(20));
INSERT INTO fruits VALUES (1, 'Apple'), (2, 'Banana'), (3, 'Cherry'), (4, 'Date'), (5, 'Elderberry'), (6, 'Fig'), (7, 'Grape');
SELECT id, name FROM fruits ORDER BY id OFFSET 3 ROWS;

Important Notes

OFFSET alone does not limit how many rows you get; it just skips rows.

Use OFFSET with LIMIT (or FETCH) to get a fixed number of rows after skipping.

Without ORDER BY, OFFSET may return unpredictable results.

Summary

OFFSET skips a set number of rows in query results.

Always use ORDER BY with OFFSET for consistent paging.

Combine OFFSET with LIMIT to control page size.