OFFSET for pagination in SQL - Step-by-Step Execution

Concept Flow - OFFSET for pagination

Start Query

↓

Apply ORDER BY

↓

Skip OFFSET rows

↓

Return LIMIT rows

↓

Display Results

↓

End

The query orders data, skips a number of rows using OFFSET, then returns a limited number of rows for pagination.

Execution Sample

SQL

SELECT name FROM employees ORDER BY id LIMIT 3 OFFSET 2;

This query gets 3 employee names, skipping the first 2 rows after ordering by id.

Execution Table

Step	Action	Rows Considered	Rows Skipped (OFFSET)	Rows Returned (LIMIT)	Output Rows
1	Start query execution	All employees (5 rows)	0	0	None yet
2	Order rows by id	All employees ordered by id	0	0	id=1,2,3,4,5
3	Skip OFFSET rows	Ordered rows	2	0	id=3,4,5 remain
4	Return LIMIT rows	Remaining rows after OFFSET	2	3	id=3,4,5
5	Display results	Final output	2	3	Names of employees with id 3,4,5
6	End	Query complete	2	3	3 rows returned

💡 OFFSET 2 skips first 2 rows; LIMIT 3 returns next 3 rows; query ends after returning these rows.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Rows Considered	5 unordered	5 ordered by id	3 after skipping 2	3 limited to 3	3 final rows
Output Rows	None	None	None	3 rows selected	3 rows displayed

Key Moments - 2 Insights

Why does OFFSET skip rows before LIMIT selects them?

OFFSET tells the database how many rows to skip first, then LIMIT picks how many rows to return from the remaining rows, as shown in execution_table rows 3 and 4.

What happens if OFFSET is larger than total rows?

No rows remain after skipping, so LIMIT returns zero rows. This is why OFFSET must be less than total rows to get results, seen in execution_table step 3.

Visual Quiz - 3 Questions

Look at the execution table, how many rows are returned after applying OFFSET and LIMIT?

A 2 rows

B 3 rows

C 5 rows

D 0 rows

💡 Hint

Check execution_table row 4 'Rows Returned (LIMIT)' column.

At which step does the query skip the first 2 rows?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Look at execution_table row 3 'Action' column.

If LIMIT was changed to 2, how many rows would be returned after OFFSET?

A 2 rows

B 3 rows

C 5 rows

D 0 rows

💡 Hint

LIMIT controls how many rows are returned after OFFSET, see execution_table row 4.

Concept Snapshot

OFFSET skips a number of rows before returning results.
LIMIT sets how many rows to return.
Use ORDER BY to define row order.
Common for pagination: OFFSET = (page-1)*page_size.
Example: SELECT * FROM table ORDER BY id LIMIT 10 OFFSET 20;

Full Transcript

This visual execution shows how SQL OFFSET works for pagination. The query orders all rows by id, then skips the first 2 rows using OFFSET 2. After skipping, it returns the next 3 rows using LIMIT 3. The execution table traces each step: starting with all rows, ordering them, skipping rows, then returning limited rows. Variables track how many rows remain after each step. Key moments clarify why OFFSET skips before LIMIT selects, and what happens if OFFSET is too large. The quiz tests understanding of rows skipped and returned at each step. OFFSET and LIMIT together help show pages of data in a controlled way.

Practice

1. What does the OFFSET clause do in an SQL query?

easy

A. It skips a specified number of rows before returning the result set.

B. It limits the number of rows returned by the query.

C. It sorts the rows in ascending order.

D. It groups rows based on a column.

Solution

1. Step 1: Understand OFFSET purpose

   The OFFSET clause tells SQL to skip a certain number of rows before starting to return rows.

2. Step 2: Compare with other clauses

   LIMIT controls how many rows to return, ORDER BY sorts rows, GROUP BY groups rows. OFFSET specifically skips rows.

3. Final Answer:

   It skips a specified number of rows before returning the result set. -> Option A

4. Quick Check:

   OFFSET = skip rows [OK]

Hint: OFFSET skips rows; LIMIT controls count [OK]

Common Mistakes:

• Confusing OFFSET with LIMIT
• Thinking OFFSET sorts rows
• Assuming OFFSET groups rows

2. Which of the following is the correct syntax to skip the first 10 rows in a query?

easy

A. SELECT * FROM table OFFSET;

B. SELECT * FROM table SKIP 10;

C. SELECT * FROM table LIMIT 10 OFFSET;

D. SELECT * FROM table OFFSET 10;

Solution

1. Step 1: Recall OFFSET syntax

   The correct syntax is to write OFFSET followed by the number of rows to skip, e.g., OFFSET 10.

2. Step 2: Check each option

   SELECT * FROM table OFFSET 10; uses correct syntax. SELECT * FROM table SKIP 10; uses SKIP which is invalid. SELECT * FROM table LIMIT 10 OFFSET; misplaces OFFSET. SELECT * FROM table OFFSET; lacks a number.

3. Final Answer:

   SELECT * FROM table OFFSET 10; -> Option D

4. Quick Check:

   OFFSET number correct syntax [OK]

Hint: Use OFFSET number after FROM clause [OK]

Common Mistakes:

• Using SKIP instead of OFFSET
• Omitting the number after OFFSET
• Placing OFFSET before FROM

3. Given the table Employees with columns id and name, what will this query return?

SELECT id, name FROM Employees ORDER BY id ASC LIMIT 3 OFFSET 2;

medium

A. Rows with the first 3 employees ordered by id.

B. Rows 1, 2, and 3 ordered by id ascending.

C. Rows 3, 4, and 5 ordered by id ascending.

D. Rows 2, 3, and 4 ordered by id ascending.

Solution

1. Step 1: Understand OFFSET and LIMIT effect

   OFFSET 2 skips the first 2 rows. LIMIT 3 returns the next 3 rows after skipping.

2. Step 2: Apply ORDER BY

   Rows are ordered by id ascending, so rows with id 3, 4, and 5 are returned.

3. Final Answer:

   Rows 3, 4, and 5 ordered by id ascending. -> Option C

4. Quick Check:

   OFFSET 2 skips first 2, LIMIT 3 returns next 3 [OK]

Hint: OFFSET skips rows, LIMIT picks next rows [OK]

Common Mistakes:

• Ignoring OFFSET and returning first rows
• Confusing OFFSET number with LIMIT
• Not applying ORDER BY before OFFSET

4. Identify the error in this query:

SELECT * FROM Products LIMIT 5 OFFSET;

medium

A. LIMIT cannot be used with OFFSET.

B. OFFSET requires a number after it.

C. SELECT * is not allowed with OFFSET.

D. OFFSET must come before LIMIT.

Solution

1. Step 1: Check OFFSET syntax

   OFFSET must be followed by a number indicating how many rows to skip.

2. Step 2: Analyze the query

   The query ends with OFFSET but no number is given, causing a syntax error.

3. Final Answer:

   OFFSET requires a number after it. -> Option B

4. Quick Check:

   OFFSET needs number [OK]

Hint: Always put a number after OFFSET [OK]

Common Mistakes:

• Leaving OFFSET without a number
• Thinking OFFSET must come before LIMIT
• Believing SELECT * disallows OFFSET

5. You want to display page 4 of a product list with 20 items per page, ordered by created_at descending. Which query correctly uses OFFSET and LIMIT?

hard

A. SELECT * FROM Products ORDER BY created_at DESC LIMIT 20 OFFSET 60;

B. SELECT * FROM Products ORDER BY created_at DESC LIMIT 20 OFFSET 80;

C. SELECT * FROM Products ORDER BY created_at DESC LIMIT 4 OFFSET 20;

D. SELECT * FROM Products ORDER BY created_at DESC OFFSET 20 LIMIT 4;

Solution

1. Step 1: Calculate OFFSET for page 4

   Each page has 20 items, so page 4 starts after 3 pages: 3 * 20 = 60 rows skipped.

2. Step 2: Check LIMIT and OFFSET usage

   LIMIT 20 returns 20 rows, OFFSET 60 skips first 60 rows, so page 4 shows rows 61-80.

3. Step 3: Verify ORDER BY

   ORDER BY created_at DESC ensures newest products first, consistent paging.

4. Final Answer:

   SELECT * FROM Products ORDER BY created_at DESC LIMIT 20 OFFSET 60; -> Option A

5. Quick Check:

   Page 4 offset = 60, limit = 20 [OK]

Hint: OFFSET = (page-1)*page_size, LIMIT = page_size [OK]

Common Mistakes:

• Calculating OFFSET as page*page_size
• Swapping LIMIT and OFFSET values
• Omitting ORDER BY for consistent paging