Using OFFSET to Skip Rows and Build Pagination

Let me ask you a question: what do you do when you find way too many products on one page of an online store? Right, you open the next page. But what’s happening behind the scenes? That’s where SQL magic comes in — the OFFSET command is used to skip rows. Today you’ll find out what OFFSET is, why you need it, how to use it, and why it’s the foundation of data pagination.

OFFSET is a keyword in SQL that lets you skip a specified number of rows in your query results. It’s like flipping through a book: you can “skip” the first 10 rows and start looking from the 11th.

Syntax

SELECT column1, column2
FROM table
OFFSET number_of_rows;

• OFFSET — the keyword for skipping rows.
• number_of_rows — how many rows you want to skip.

Simple Example of Using OFFSET

Let’s say we have a students table with the following data:

We want to show all students starting from the third one. For that, we can use this query:

SELECT *
FROM students
OFFSET 2;

Result:

SQL “skipped” the first two rows (Alice and Bob) and gave us the result starting from the third row. Just like using bookmarks: “we already read this page, let’s open the next one.”

Combining OFFSET with LIMIT

OFFSET is usually used together with LIMIT. This lets you skip rows and also limit the result to the number of records you want. This is super useful for building pagination — showing data in chunks, a few rows at a time.

Let’s say we want to show 2 records at a time and start from the third row. The query would look like this:

SELECT *
FROM students
LIMIT 2 
OFFSET 2;

Result:

How it works:

1. OFFSET 2 skips the first two rows: Alice and Bob.
2. LIMIT 2 grabs just two rows from what’s left.

Building Pagination

Now we’re at the most interesting part — building pagination. Imagine you’re making a web app with a list of students. You want to show 2 records per page. How do you do that?

The main idea:

• The first page skips 0 rows: OFFSET 0.
• The second page skips 2 rows: OFFSET 2.
• The third page skips 4 rows: OFFSET 4.
• And so on...

Example: Show the Second Page

The first page shows records 1 and 2 (Alice and Bob). The second page — records 3 and 4 (Clara and Dan).

SELECT *
FROM students
ORDER BY id
LIMIT 2 
OFFSET 2;

Result:

Example: Show the Third Page

The third page — records 5 and 6 (if they exist).

SELECT *
FROM students
ORDER BY id
LIMIT 2 
OFFSET 4;

Result:

Formula for Calculating OFFSET

When building pagination systems, you can use this formula to automate things:

OFFSET = (page_number - 1) * records_per_page

For example:

• For the first page: (1 - 1) * 2 = 0.
• For the second page: (2 - 1) * 2 = 2.
• For the third page: (3 - 1) * 2 = 4.

Important Performance Notes

When you’re working with big tables, using OFFSET can get inefficient, especially on later pages. The reason is that PostgreSQL still scans through the rows you’re skipping. For example, a query with OFFSET 10000 makes the DBMS go through the first 10,000 rows before giving you results. In those cases, you might want to look at alternatives, like using a unique identifier as a pagination marker.

Alternative: Cursor-Based Pagination

For optimization, you can use a “cursor” approach. Instead of skipping rows with OFFSET, you can remember the IDs of the last row you got on the previous page and use them to build the next query:

SELECT *
FROM students
WHERE id > last_displayed_id
ORDER BY id
LIMIT 2;

This approach can really speed things up with big tables.

Practical Uses for Pagination

Pagination is used in most web apps: online stores, blogs, admin panels. For example:

• Showing a list of products in an online store;
• Displaying a list of users in a CRM system;
• Paginated news feed.

Pagination can also be handy when analyzing big data sets, so you can work with small chunks at a time.

Common Mistakes When Using OFFSET

Working with OFFSET can be tricky sometimes, especially when you’re just learning. Here are some common mistakes:

No sorting. If you don’t add ORDER BY, the order of rows in OFFSET results can be unpredictable.

Wrong query:

SELECT * FROM students
OFFSET 5;

Correct query:

SELECT * FROM students
ORDER BY id
OFFSET 5;

Wrong OFFSET value. If you set the value too high, you’ll get an empty result set.

Performance issues. Like we talked about earlier, using big OFFSET values on later pages can be inefficient.

No filters. If you use OFFSET and LIMIT without filtering (WHERE), you might get unnecessary data, which can hurt performance.

Operator order. The convention is to write LIMIT first, then OFFSET, and nearly everybody does. However PostgreSQL also allows the reverse order (OFFSET 10 LIMIT 5) — this is documented (see PG 17 docs). You can also leave operators out entirely.

2

Task

SQL SELF, level 3, lesson 1

Locked

Skipping rows using `OFFSET`

Skipping rows using `OFFSET`

Open

2

Task

SQL SELF, level 3, lesson 1

Locked

Paginated Data Output

Paginated Data Output

Open