LIMIT and OFFSET pagination in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the LIMIT clause do in a SQL query?

The LIMIT clause restricts the number of rows returned by the query to a specified maximum number.

beginner

What is the purpose of the OFFSET clause in SQL?

The OFFSET clause skips a specified number of rows before starting to return rows from the query result.

intermediate

How do LIMIT and OFFSET work together for pagination?

You use LIMIT to set how many rows to show per page and OFFSET to skip rows from previous pages, enabling you to fetch one page of results at a time.

intermediate

Write a SQL query to get the 3rd page of results if each page shows 10 rows.

SELECT * FROM table_name ORDER BY id LIMIT 10 OFFSET 20; Explanation: OFFSET is (page_number - 1) * page_size = (3 - 1) * 10 = 20.

intermediate

Why is it important to use ORDER BY with LIMIT and OFFSET?

Without ORDER BY, the order of rows is not guaranteed, so pagination might return inconsistent or repeated rows across pages.

What does LIMIT 5 OFFSET 10 do in a SQL query?

A Returns 5 rows starting from the 11th row

B Returns 10 rows starting from the 6th row

C Returns 15 rows starting from the 1st row

D Returns 10 rows starting from the 5th row

OFFSET 10 skips the first 10 rows, then LIMIT 5 returns the next 5 rows.

Which clause should you always use with LIMIT and OFFSET for consistent pagination?

A GROUP BY

B HAVING

C WHERE

D ORDER BY

ORDER BY ensures rows are returned in a consistent order for pagination.

If you want to get the first 20 rows of a table, which SQL clause would you use?

A OFFSET 20 LIMIT 0

B LIMIT 20 OFFSET 0

C LIMIT 0 OFFSET 20

D OFFSET 0 LIMIT 0

LIMIT 20 OFFSET 0 returns the first 20 rows starting from the beginning.

What happens if you omit OFFSET in a query with LIMIT?

A The query returns the first <code>LIMIT</code> rows

B The query returns no rows

C The query returns all rows

D The query returns rows starting from the last row

Without OFFSET, the query returns rows starting from the first row up to the LIMIT number.

How do you calculate the OFFSET for page 5 if each page shows 25 rows?

A 75

B 125

C 100

D 5

OFFSET = (page_number - 1) * page_size = (5 - 1) * 25 = 100. Correct answer is B.

Explain how to use LIMIT and OFFSET to show page 4 of a list where each page has 15 items.

Think about how many rows to skip before starting page 4.

Why is it important to include ORDER BY when using LIMIT and OFFSET for pagination?

Consider what happens if the database returns rows in different orders each time.

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