LIMIT and OFFSET pagination in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What rows are returned by this query?

Consider a table employees with 10 rows numbered 1 to 10 by id. What rows does this query return?

SELECT * FROM employees ORDER BY id ASC LIMIT 3 OFFSET 4;

PostgreSQL

SELECT * FROM employees ORDER BY id ASC LIMIT 3 OFFSET 4;

A Rows with id 5, 6, and 7

B Rows with id 1, 2, and 3

C Rows with id 4, 5, and 6

D Rows with id 6, 7, and 8

💡 Hint

OFFSET skips the first N rows, LIMIT picks how many rows after that.

OFFSET 4 skips the first 4 rows (id 1 to 4). LIMIT 3 then returns the next 3 rows (id 5, 6, 7).

🧠 Conceptual

intermediate

Why use OFFSET with LIMIT for pagination?

Which is the best reason to use LIMIT with OFFSET in a query for pagination?

A To delete rows after a certain position

B To update rows in batches

C To skip a set number of rows and then return a fixed number of rows for a page

D To group rows by a column

💡 Hint

Think about showing results page by page.

OFFSET skips rows, LIMIT controls how many rows to show, perfect for pages.

📝 Syntax

advanced

Identify the syntax error in this pagination query

Which option shows the correct syntax for paginating results with LIMIT and OFFSET in PostgreSQL?

PostgreSQL

SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 20;

A SELECT * FROM products ORDER BY price DESC OFFSET 20 LIMIT 10;

B SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 20;

C SELECT * FROM products ORDER BY price DESC LIMIT OFFSET 20 10;

D SELECT * FROM products ORDER BY price DESC LIMIT 10, OFFSET 20;

💡 Hint

LIMIT comes before OFFSET in PostgreSQL syntax.

PostgreSQL requires LIMIT first, then OFFSET. Option B is correct syntax.

❓ optimization

advanced

Why can OFFSET cause performance issues on large tables?

What is the main reason using a large OFFSET value can slow down pagination queries?

A OFFSET duplicates rows in the result set

B OFFSET causes the database to lock the entire table

C OFFSET disables the use of indexes completely

D The database must scan and skip all rows before the OFFSET, even if not returned

💡 Hint

Think about how the database finds the starting row.

OFFSET makes the database scan and discard rows before the offset, which is slow for large offsets.

🔧 Debug

expert

Why does this pagination query return duplicate rows on page 2?

Given this query for page 2:

SELECT * FROM orders ORDER BY created_at LIMIT 10 OFFSET 10;

Sometimes rows appear duplicated across pages. What is the most likely cause?

A The ORDER BY column <code>created_at</code> has duplicate values causing unstable ordering

B The OFFSET value is too small

C LIMIT and OFFSET are reversed in the query

D The table has no primary key

💡 Hint

Think about how rows with the same order value are sorted.

If ORDER BY column has duplicates, the order of those rows is not guaranteed, causing duplicates across pages.