FETCH FIRST for SQL standard pagination in PostgreSQL - Step-by-Step Execution

Concept Flow - FETCH FIRST for SQL standard pagination

Start Query Execution

↓

Apply WHERE conditions

↓

Apply ORDER BY if present

↓

Apply FETCH FIRST N ROWS ONLY

↓

Return limited result set

↓

End

The query runs filtering and sorting first, then FETCH FIRST limits the number of rows returned.

Execution Sample

PostgreSQL

SELECT * FROM employees ORDER BY id FETCH FIRST 3 ROWS ONLY;

This query returns the first 3 rows from employees table ordered by id.

Execution Table

Step	Action	Intermediate Result	Rows Affected
1	Start query execution	All employees table rows	10
2	Apply ORDER BY id	Employees sorted by id ascending	10
3	Apply FETCH FIRST 3 ROWS ONLY	Limit result to first 3 rows	3
4	Return result set	3 rows returned	3
5	End	Query complete	3

💡 FETCH FIRST limits output to 3 rows, stopping further row retrieval.

Variable Tracker

Variable	Start	After ORDER BY	After FETCH FIRST	Final
rows_in_result	10	10 (sorted)	3 (limited)	3

Key Moments - 3 Insights

Why does FETCH FIRST come after ORDER BY?

Because ordering must happen before limiting rows, so the top N rows are the correct ones. See execution_table step 2 and 3.

Does FETCH FIRST change the original table data?

No, FETCH FIRST only limits the output rows of the query, it does not modify the table. See execution_table step 4.

What happens if FETCH FIRST number is larger than total rows?

The query returns all rows without error, just fewer than the FETCH FIRST number. This is implied by step 3 and 4.

Visual Quiz - 3 Questions

Look at the execution table, how many rows are returned after applying FETCH FIRST?

A 3

B 10

C 0

D All rows

💡 Hint

Check execution_table row 3 and 4 for rows affected after FETCH FIRST.

At which step does the query sort the rows?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at execution_table step 2 description about ORDER BY.

If FETCH FIRST was removed, how would the final rows affected change?

A It would still be 3

B It would be 0

C It would be 10

D It would cause an error

💡 Hint

Refer to variable_tracker rows_in_result after ORDER BY and before FETCH FIRST.

Concept Snapshot

FETCH FIRST N ROWS ONLY limits query output to N rows.
It must come after ORDER BY to get correct top rows.
Does not modify table data, only limits result.
If N > total rows, returns all rows without error.
Syntax example: SELECT * FROM table ORDER BY col FETCH FIRST 5 ROWS ONLY;

Full Transcript

The FETCH FIRST clause in SQL is used to limit the number of rows returned by a query. The query first applies any WHERE filters and then sorts the rows if ORDER BY is present. After sorting, FETCH FIRST N ROWS ONLY restricts the output to the first N rows. This does not change the underlying table data, only the query result. If the number specified in FETCH FIRST is larger than the total rows available, all rows are returned without error. This is useful for pagination or getting a sample of rows. The execution steps show starting with all rows, sorting them, then limiting to the first N rows, and finally returning those rows as the result.

Practice

1. What does the SQL clause FETCH FIRST 5 ROWS ONLY do in a query?

easy

A. Limits the query result to only the first 5 rows.

B. Skips the first 5 rows and returns the rest.

C. Returns all rows except the first 5.

D. Sorts the rows by the first 5 columns.

Solution

1. Step 1: Understand FETCH FIRST clause

   The clause FETCH FIRST 5 ROWS ONLY limits the number of rows returned by the query to 5.

2. Step 2: Effect on query result

   This means only the first 5 rows from the result set will be returned, no more.

3. Final Answer:

   Limits the query result to only the first 5 rows. -> Option A

4. Quick Check:

   FETCH FIRST limits rows = A [OK]

Hint: FETCH FIRST means limit rows returned, not skip [OK]

Common Mistakes:

• Confusing FETCH FIRST with OFFSET
• Thinking FETCH FIRST sorts rows automatically
• Assuming FETCH FIRST removes rows instead of limiting

2. Which of the following is the correct syntax to get the first 10 rows from a table named employees in PostgreSQL?

easy

A. SELECT * FROM employees FETCH 10 ROWS;

B. SELECT * FROM employees FETCH FIRST 10 ROWS ONLY;

C. SELECT * FROM employees LIMIT FIRST 10;

D. SELECT * FROM employees TOP 10;

Solution

1. Step 1: Recall correct FETCH FIRST syntax

   The standard SQL syntax to limit rows is FETCH FIRST n ROWS ONLY. PostgreSQL supports this.

2. Step 2: Check each option

   SELECT * FROM employees FETCH FIRST 10 ROWS ONLY; uses correct syntax. Options A, B, and D are invalid in PostgreSQL.

3. Final Answer:

   SELECT * FROM employees FETCH FIRST 10 ROWS ONLY; -> Option B

4. Quick Check:

   Correct FETCH FIRST syntax = C [OK]

Hint: Use FETCH FIRST n ROWS ONLY for standard limit [OK]

Common Mistakes:

• Using LIMIT FIRST instead of FETCH FIRST
• Using TOP keyword which is SQL Server syntax
• Omitting ROWS ONLY after FETCH FIRST

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

SELECT id FROM products ORDER BY price DESC FETCH FIRST 3 ROWS ONLY;

medium

A. All products sorted by price descending.

B. The 3 products with the lowest prices.

C. The 3 products with the highest prices.

D. Syntax error due to FETCH FIRST usage.

Solution

1. Step 1: Analyze ORDER BY clause

   The query orders products by price in descending order, so highest prices come first.

2. Step 2: Apply FETCH FIRST 3 ROWS ONLY

   This limits the output to only the first 3 rows after sorting, i.e., top 3 highest priced products.

3. Final Answer:

   The 3 products with the highest prices. -> Option C

4. Quick Check:

   ORDER BY DESC + FETCH FIRST = top rows [OK]

Hint: ORDER BY DESC + FETCH FIRST = top N highest [OK]

Common Mistakes:

• Ignoring ORDER BY and thinking it returns random rows
• Confusing DESC with ASC order
• Assuming FETCH FIRST causes syntax error

4. Identify the error in this query:

SELECT name FROM customers FETCH FIRST 5 ROWS;

medium

A. Missing ORDER BY clause before FETCH FIRST.

B. No error; query is valid.

C. FETCH FIRST cannot be used without OFFSET.

D. Incorrect FETCH FIRST syntax; missing 'ONLY'.

Solution

1. Step 1: Check FETCH FIRST syntax

   The correct syntax requires ROWS ONLY after the number, so it should be FETCH FIRST 5 ROWS ONLY.

2. Step 2: Verify other parts

   ORDER BY is recommended but not mandatory. OFFSET is not required. So the error is missing 'ONLY'.

3. Final Answer:

   Incorrect FETCH FIRST syntax; missing 'ONLY'. -> Option D

4. Quick Check:

   FETCH FIRST needs ROWS ONLY [OK]

Hint: FETCH FIRST must end with ROWS ONLY [OK]

Common Mistakes:

• Omitting 'ONLY' after FETCH FIRST clause
• Thinking ORDER BY is mandatory with FETCH FIRST
• Confusing FETCH FIRST with OFFSET usage

5. You want to show page 3 of a product list with 10 items per page, sorted by created_at ascending. Which query correctly uses FETCH FIRST and OFFSET for this pagination?

hard

A. SELECT * FROM products ORDER BY created_at ASC OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

B. SELECT * FROM products ORDER BY created_at ASC FETCH FIRST 10 ROWS OFFSET 20 ROWS ONLY;

C. SELECT * FROM products ORDER BY created_at ASC FETCH FIRST 30 ROWS ONLY;

D. SELECT * FROM products ORDER BY created_at ASC OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY;

Solution

1. Step 1: Calculate OFFSET for page 3

   Each page has 10 items, so page 3 starts at row 21. OFFSET is zero-based, so OFFSET = (3-1)*10 = 20.

2. Step 2: Use FETCH FIRST to limit rows per page

   FETCH FIRST 10 ROWS ONLY limits the result to 10 rows for the page.

3. Step 3: Check query syntax order

   OFFSET must come before FETCH FIRST. SELECT * FROM products ORDER BY created_at ASC OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY; correctly orders OFFSET then FETCH FIRST.

4. Final Answer:

   SELECT * FROM products ORDER BY created_at ASC OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY; -> Option A

5. Quick Check:

   OFFSET before FETCH FIRST for pagination [OK]

Hint: OFFSET = (page-1)*size; FETCH FIRST = page size [OK]

Common Mistakes:

• Swapping OFFSET and FETCH FIRST order
• Using FETCH FIRST without OFFSET for pages >1
• Incorrect OFFSET calculation for page number