OFFSET for pagination in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the OFFSET clause do in an SQL query?

The OFFSET clause skips a specified number of rows before starting to return rows from the query result. It helps to move forward in the list of results.

beginner

How do OFFSET and LIMIT work together for pagination?

LIMIT sets how many rows to return, and OFFSET sets how many rows to skip before starting to return rows. Together, they let you fetch a specific page of results.

beginner

Write a simple SQL query to get 10 rows starting from the 21st row.

SELECT * FROM table_name ORDER BY column_name LIMIT 10 OFFSET 20;

intermediate

Why is it important to use ORDER BY with OFFSET?

ORDER BY ensures the rows are returned in a consistent order. Without it, OFFSET might skip unpredictable rows because the order is not guaranteed.

intermediate

What happens if you use OFFSET without LIMIT?

The query skips the specified number of rows and returns all remaining rows after that. This can return a large number of rows if LIMIT is not set.

What does OFFSET 10 do in an SQL query?

A Returns the first 10 rows

B Skips the first 10 rows

C Limits the result to 10 rows

D Orders the rows by 10

OFFSET 10 skips the first 10 rows and starts returning rows after that.

Which clause should you use with OFFSET to get a specific page of results?

A LIMIT

B HAVING

C GROUP BY

D WHERE

LIMIT sets how many rows to return, so combined with OFFSET it fetches a page of results.

What is the result of this query? SELECT * FROM users ORDER BY id LIMIT 5 OFFSET 10;

A First 5 users ordered by id

B Users 6 to 10 ordered by id

C Users 11 to 15 ordered by id

D All users after skipping 10

OFFSET 10 skips first 10 rows, LIMIT 5 returns next 5 rows, so rows 11 to 15.

What happens if you omit ORDER BY when using OFFSET?

A OFFSET is ignored

B Query will fail

C Rows are always ordered by primary key

D Rows are returned in random order

Without ORDER BY, the row order is not guaranteed, so OFFSET may skip unpredictable rows.

Can OFFSET be zero? What does OFFSET 0 mean?

A Yes, OFFSET 0 means skip zero rows and start from the first row

B OFFSET 0 is the same as LIMIT 0

C OFFSET 0 returns no rows

D No, OFFSET cannot be zero

OFFSET 0 means do not skip any rows; start returning from the first row.

Explain how OFFSET and LIMIT work together to implement pagination in SQL.

Think about how you would show page 3 of a list with 10 items per page.

Describe why using ORDER BY is important when using OFFSET in a query.

Imagine flipping through pages of a book with shuffled pages.

Practice

1. What does the OFFSET clause do in an SQL query?

easy

A. It skips a specified number of rows before returning the result set.

B. It limits the number of rows returned by the query.

C. It sorts the rows in ascending order.

D. It groups rows based on a column.

Solution

1. Step 1: Understand OFFSET purpose

   The OFFSET clause tells SQL to skip a certain number of rows before starting to return rows.

2. Step 2: Compare with other clauses

   LIMIT controls how many rows to return, ORDER BY sorts rows, GROUP BY groups rows. OFFSET specifically skips rows.

3. Final Answer:

   It skips a specified number of rows before returning the result set. -> Option A

4. Quick Check:

   OFFSET = skip rows [OK]

Hint: OFFSET skips rows; LIMIT controls count [OK]

Common Mistakes:

• Confusing OFFSET with LIMIT
• Thinking OFFSET sorts rows
• Assuming OFFSET groups rows

2. Which of the following is the correct syntax to skip the first 10 rows in a query?

easy

A. SELECT * FROM table OFFSET;

B. SELECT * FROM table SKIP 10;

C. SELECT * FROM table LIMIT 10 OFFSET;

D. SELECT * FROM table OFFSET 10;

Solution

1. Step 1: Recall OFFSET syntax

   The correct syntax is to write OFFSET followed by the number of rows to skip, e.g., OFFSET 10.

2. Step 2: Check each option

   SELECT * FROM table OFFSET 10; uses correct syntax. SELECT * FROM table SKIP 10; uses SKIP which is invalid. SELECT * FROM table LIMIT 10 OFFSET; misplaces OFFSET. SELECT * FROM table OFFSET; lacks a number.

3. Final Answer:

   SELECT * FROM table OFFSET 10; -> Option D

4. Quick Check:

   OFFSET number correct syntax [OK]

Hint: Use OFFSET number after FROM clause [OK]

Common Mistakes:

• Using SKIP instead of OFFSET
• Omitting the number after OFFSET
• Placing OFFSET before FROM

3. Given the table Employees with columns id and name, what will this query return?

SELECT id, name FROM Employees ORDER BY id ASC LIMIT 3 OFFSET 2;

medium

A. Rows with the first 3 employees ordered by id.

B. Rows 1, 2, and 3 ordered by id ascending.

C. Rows 3, 4, and 5 ordered by id ascending.

D. Rows 2, 3, and 4 ordered by id ascending.

Solution

1. Step 1: Understand OFFSET and LIMIT effect

   OFFSET 2 skips the first 2 rows. LIMIT 3 returns the next 3 rows after skipping.

2. Step 2: Apply ORDER BY

   Rows are ordered by id ascending, so rows with id 3, 4, and 5 are returned.

3. Final Answer:

   Rows 3, 4, and 5 ordered by id ascending. -> Option C

4. Quick Check:

   OFFSET 2 skips first 2, LIMIT 3 returns next 3 [OK]

Hint: OFFSET skips rows, LIMIT picks next rows [OK]

Common Mistakes:

• Ignoring OFFSET and returning first rows
• Confusing OFFSET number with LIMIT
• Not applying ORDER BY before OFFSET

4. Identify the error in this query:

SELECT * FROM Products LIMIT 5 OFFSET;

medium

A. LIMIT cannot be used with OFFSET.

B. OFFSET requires a number after it.

C. SELECT * is not allowed with OFFSET.

D. OFFSET must come before LIMIT.

Solution

1. Step 1: Check OFFSET syntax

   OFFSET must be followed by a number indicating how many rows to skip.

2. Step 2: Analyze the query

   The query ends with OFFSET but no number is given, causing a syntax error.

3. Final Answer:

   OFFSET requires a number after it. -> Option B

4. Quick Check:

   OFFSET needs number [OK]

Hint: Always put a number after OFFSET [OK]

Common Mistakes:

• Leaving OFFSET without a number
• Thinking OFFSET must come before LIMIT
• Believing SELECT * disallows OFFSET

5. You want to display page 4 of a product list with 20 items per page, ordered by created_at descending. Which query correctly uses OFFSET and LIMIT?

hard

A. SELECT * FROM Products ORDER BY created_at DESC LIMIT 20 OFFSET 60;

B. SELECT * FROM Products ORDER BY created_at DESC LIMIT 20 OFFSET 80;

C. SELECT * FROM Products ORDER BY created_at DESC LIMIT 4 OFFSET 20;

D. SELECT * FROM Products ORDER BY created_at DESC OFFSET 20 LIMIT 4;

Solution

1. Step 1: Calculate OFFSET for page 4

   Each page has 20 items, so page 4 starts after 3 pages: 3 * 20 = 60 rows skipped.

2. Step 2: Check LIMIT and OFFSET usage

   LIMIT 20 returns 20 rows, OFFSET 60 skips first 60 rows, so page 4 shows rows 61-80.

3. Step 3: Verify ORDER BY

   ORDER BY created_at DESC ensures newest products first, consistent paging.

4. Final Answer:

   SELECT * FROM Products ORDER BY created_at DESC LIMIT 20 OFFSET 60; -> Option A

5. Quick Check:

   Page 4 offset = 60, limit = 20 [OK]

Hint: OFFSET = (page-1)*page_size, LIMIT = page_size [OK]

Common Mistakes:

• Calculating OFFSET as page*page_size
• Swapping LIMIT and OFFSET values
• Omitting ORDER BY for consistent paging