LIMIT and OFFSET pagination in PostgreSQL - Step-by-Step Execution

Concept Flow - LIMIT and OFFSET pagination

Start Query

↓

Apply ORDER BY

↓

Apply OFFSET

↓

Skip N rows

↓

Apply LIMIT

↓

Return M rows

↓

End Query

The query first orders rows using ORDER BY, then skips a number of rows using OFFSET, then returns a limited number of rows using LIMIT.

Execution Sample

PostgreSQL

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

This query returns 3 rows from the employees table, skipping the first 2 rows.

Execution Table

Step	Action	Rows Considered	Rows Skipped (OFFSET)	Rows Returned (LIMIT)	Resulting Rows IDs
1	Start query execution	All rows in employees	0	0	All rows
2	Apply ORDER BY id	All rows sorted by id	0	0	[1, 2, 3, 4, 5, 6]
3	Apply OFFSET 2	Sorted rows	2	0	[3, 4, 5, 6]
4	Apply LIMIT 3	Rows after offset	2	3	[3, 4, 5]
5	Return result	Final rows	2	3	[3, 4, 5]

💡 Query ends after returning 3 rows starting from the 3rd row (offset 2).

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Rows Considered	All rows	Sorted by id	After skipping 2 rows	Limited to 3 rows	Final result set
Resulting Rows IDs	[]	[1, 2, 3, 4, 5, 6]	[3, 4, 5, 6]	[3, 4, 5]	[3, 4, 5]

Key Moments - 2 Insights

Why does OFFSET skip rows before LIMIT is applied?

OFFSET tells the database to skip a number of rows first, then LIMIT restricts how many rows to return from the remaining rows. See execution_table rows 3 and 4.

What happens if OFFSET is larger than the total number of rows?

No rows remain after skipping, so the query returns an empty result. This is because OFFSET skips that many rows first, leaving none to LIMIT. This is implied after step 3 if offset > total rows.

Visual Quiz - 3 Questions

Look at the execution_table, what rows are returned after applying OFFSET 2 and LIMIT 3?

A [1, 2, 3]

B [4, 5, 6]

C [3, 4, 5]

D [2, 3, 4]

💡 Hint

Check rows 3 and 4 in the execution_table for the resulting rows after OFFSET and LIMIT.

At which step does the query skip rows?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look at the 'Rows Skipped (OFFSET)' column in the execution_table.

If LIMIT was changed to 2, how would the final rows change?

A [3, 4]

B [3, 4, 5]

C [2, 3]

D [1, 2]

💡 Hint

Refer to variable_tracker and see how LIMIT controls the number of rows returned after OFFSET.

Concept Snapshot

LIMIT and OFFSET control pagination in SQL.
OFFSET skips a number of rows first.
LIMIT restricts how many rows to return after skipping.
Use ORDER BY to define row order.
Example: SELECT * FROM table ORDER BY id LIMIT 3 OFFSET 2;

Full Transcript

This visual execution shows how LIMIT and OFFSET work together to paginate query results. The query first orders all rows by id. Then OFFSET skips the first 2 rows. After skipping, LIMIT returns the next 3 rows. The execution table tracks each step, showing how rows are filtered. Variable tracking shows the rows considered and resulting rows after each step. Key moments clarify why OFFSET applies before LIMIT and what happens if OFFSET exceeds total rows. The quiz tests understanding of which rows are returned and when skipping happens. The snapshot summarizes the syntax and behavior for quick reference.

Practice

1. What does the LIMIT clause do in a PostgreSQL query?

easy

A. It sorts the rows in ascending order.

B. It controls how many rows the query returns.

C. It skips a number of rows before returning results.

D. It groups rows based on a column.

Solution

1. Step 1: Understand the role of LIMIT

   The LIMIT clause restricts the number of rows returned by a query to a specified count.

2. Step 2: Differentiate from OFFSET

   OFFSET skips rows, but LIMIT controls the count of rows returned after skipping.

3. Final Answer:

   It controls how many rows the query returns. -> Option B

4. Quick Check:

   LIMIT = row count control [OK]

Hint: LIMIT sets max rows returned, OFFSET skips rows first [OK]

Common Mistakes:

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

2. Which of the following is the correct syntax to get 10 rows starting from the 6th row in PostgreSQL?

easy

A. SELECT * FROM table LIMIT 10 OFFSET 5;

B. SELECT * FROM table LIMIT 5 OFFSET 10;

C. SELECT * FROM table LIMIT 6 OFFSET 10;

D. SELECT * FROM table OFFSET 10 LIMIT 6;

Solution

1. Step 1: Understand OFFSET and LIMIT order

   OFFSET 5 skips first 5 rows, LIMIT 10 returns next 10 rows.

2. Step 2: Check syntax correctness

   PostgreSQL allows LIMIT then OFFSET; LIMIT 10 OFFSET 5 is correct.

3. Final Answer:

   SELECT * FROM table LIMIT 10 OFFSET 5; -> Option A

4. Quick Check:

   LIMIT 10 OFFSET 5 = 10 rows after skipping 5 [OK]

Hint: Use LIMIT first, then OFFSET for correct syntax [OK]

Common Mistakes:

• Swapping LIMIT and OFFSET values
• Using OFFSET larger than total rows
• Incorrect order of LIMIT and OFFSET

3. Given the table employees with 15 rows ordered by id, what rows will this query return?

SELECT * FROM employees ORDER BY id LIMIT 5 OFFSET 10;

medium

A. Rows with id 11 to 15

B. Rows with id 6 to 10

C. Rows with id 1 to 5

D. Rows with id 10 to 14

Solution

1. Step 1: Understand OFFSET 10

   OFFSET 10 skips the first 10 rows (id 1 to 10).

2. Step 2: LIMIT 5 after skipping

   LIMIT 5 returns the next 5 rows, which are id 11 to 15.

3. Final Answer:

   Rows with id 11 to 15 -> Option A

4. Quick Check:

   OFFSET 10 + LIMIT 5 = rows 11-15 [OK]

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

Common Mistakes:

• Counting OFFSET as inclusive
• Mixing row numbers with id values
• Assuming OFFSET starts at 1

4. Identify the error in this query:

SELECT * FROM products LIMIT OFFSET 5 10;

medium

A. LIMIT and OFFSET order is reversed.

B. OFFSET value must come before LIMIT value.

C. Syntax error: LIMIT must have a number before OFFSET.

D. No error, query is valid.

Solution

1. Step 1: Analyze LIMIT and OFFSET syntax

   LIMIT must be followed by a number, then OFFSET with its number.

2. Step 2: Check given query

   Query has 'LIMIT OFFSET 5 10' which is invalid syntax; LIMIT lacks a number before OFFSET.

3. Final Answer:

   Syntax error: LIMIT must have a number before OFFSET. -> Option C

4. Quick Check:

   LIMIT needs number before OFFSET [OK]

Hint: Always put LIMIT number before OFFSET number [OK]

Common Mistakes:

• Writing OFFSET before LIMIT number
• Omitting numbers after LIMIT or OFFSET
• Assuming order doesn't matter

5. You want to display page 3 of a product list, showing 20 products per page. Which query correctly uses LIMIT and OFFSET?

hard

A. SELECT * FROM products LIMIT 40 OFFSET 20;

B. SELECT * FROM products LIMIT 60 OFFSET 20;

C. SELECT * FROM products LIMIT 20 OFFSET 60;

D. SELECT * FROM products LIMIT 20 OFFSET 40;

Solution

1. Step 1: Calculate OFFSET for page 3

   Page 3 means skipping first 2 pages: 2 * 20 = 40 rows to skip.

2. Step 2: Set LIMIT to page size

   LIMIT is number of rows per page, so LIMIT 20.

3. Step 3: Form the query

   Use LIMIT 20 OFFSET 40 to get page 3 products.

4. Final Answer:

   SELECT * FROM products LIMIT 20 OFFSET 40; -> Option D

5. Quick Check:

   Page 3 offset = 40, limit = 20 [OK]

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

Common Mistakes:

• Using OFFSET as page number
• Swapping LIMIT and OFFSET values
• Calculating OFFSET incorrectly