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.

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