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.