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.