FETCH FIRST for SQL standard pagination in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Basic usage of FETCH FIRST

Given a table employees with columns id and name, what will be the output of this query?

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

PostgreSQL

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

A Returns the last 3 rows ordered by id ascending

B Returns all rows except the first 3

C Returns the first 3 rows ordered by id ascending

D Syntax error due to FETCH FIRST usage

💡 Hint

FETCH FIRST limits the number of rows returned after ordering.

FETCH FIRST 3 ROWS ONLY returns exactly the first 3 rows after sorting by id ascending.

❓ query_result

intermediate

Using OFFSET with FETCH FIRST

What does this query return?

SELECT id FROM employees ORDER BY id OFFSET 5 ROWS FETCH FIRST 2 ROWS ONLY;

PostgreSQL

SELECT id FROM employees ORDER BY id OFFSET 5 ROWS FETCH FIRST 2 ROWS ONLY;

A Rows 6 and 7 ordered by id ascending

B Rows 1 and 2 ordered by id ascending

C Rows 5 and 6 ordered by id ascending

D Syntax error due to OFFSET and FETCH FIRST combination

💡 Hint

OFFSET skips rows before FETCH FIRST limits the output.

OFFSET 5 skips first 5 rows, FETCH FIRST 2 returns next 2 rows, so rows 6 and 7 by id ascending.

📝 Syntax

advanced

Correct syntax for FETCH FIRST with PERCENT

Which option shows the correct syntax to fetch the first 10 percent of rows from a table orders ordered by order_date?

A SELECT * FROM orders ORDER BY order_date FETCH FIRST 10 PERCENT ROWS ONLY;

B SELECT * FROM orders ORDER BY order_date FETCH FIRST 10 ROWS PERCENT ONLY;

C SELECT * FROM orders ORDER BY order_date FETCH FIRST PERCENT 10 ROWS ONLY;

D SELECT * FROM orders ORDER BY order_date FETCH FIRST ROWS 10 PERCENT ONLY;

💡 Hint

The keyword PERCENT comes immediately after the number.

The correct syntax is FETCH FIRST PERCENT ROWS ONLY, so option A is valid.

🧠 Conceptual

advanced

Behavior of FETCH FIRST without ORDER BY

What is the behavior of the query below?

SELECT * FROM products FETCH FIRST 5 ROWS ONLY;

A Returns the last 5 rows from products

B Returns the first 5 rows sorted by primary key automatically

C Syntax error because ORDER BY is required with FETCH FIRST

D Returns any 5 rows from products, order is not guaranteed

💡 Hint

Without ORDER BY, row order is unpredictable.

FETCH FIRST limits rows but without ORDER BY, the rows returned can be any 5 rows.

❓ optimization

expert

Optimizing pagination with FETCH FIRST and OFFSET

Consider a large table logs with millions of rows. Which query is generally more efficient for paginating to page 10 with 20 rows per page?

A) SELECT * FROM logs ORDER BY timestamp OFFSET 180 ROWS FETCH FIRST 20 ROWS ONLY;
B) SELECT * FROM logs WHERE timestamp > (SELECT timestamp FROM logs ORDER BY timestamp OFFSET 180 ROWS FETCH FIRST 1 ROW ONLY) ORDER BY timestamp FETCH FIRST 20 ROWS ONLY;

Choose the correct statement.

A Query A is more efficient because OFFSET is simple to use

B Query B is more efficient because it avoids scanning all rows before OFFSET

C Both queries have the same performance

D Neither query works because FETCH FIRST cannot be combined with OFFSET

💡 Hint

OFFSET can cause performance issues on large tables.

Query B uses a seek method by filtering on timestamp, avoiding scanning all rows before OFFSET, improving performance.