LIMIT and OFFSET for pagination in MySQL - Mini Project: Build & Apply

LIMIT and OFFSET for pagination

📖 Scenario: You are working on a website that shows a list of books. The list is very long, so you want to show only a few books at a time on each page. This way, visitors can click to see the next set of books without loading all of them at once.

🎯 Goal: Build a SQL query that uses LIMIT and OFFSET to show only 3 books per page from a table called books.

📋 What You'll Learn

Create a table called books with columns id and title.

Insert exactly 6 books with given titles into the books table.

Write a variable page_size set to 3 to control how many books show per page.

Write a variable page_number set to 2 to select the second page of books.

Write a SQL query that selects id and title from books using LIMIT and OFFSET based on page_size and page_number.

💡 Why This Matters

🌍 Real World

Pagination is used on websites and apps to show large lists in small parts, improving speed and user experience.

💼 Career

Knowing how to paginate data with LIMIT and OFFSET is essential for backend developers and database administrators working with user interfaces.

1

Create the books table and insert data

Create a table called books with columns id (integer) and title (text). Then insert these 6 books exactly: 'The Hobbit', '1984', 'Pride and Prejudice', 'To Kill a Mockingbird', 'The Great Gatsby', and 'Moby Dick'.

MySQL

-- Create the books table
-- Your code here

Hint

Use CREATE TABLE to make the table. Use INSERT INTO books (title) VALUES (...) to add the books.

2

Set pagination variables

Create two variables: page_size set to 3 and page_number set to 2. These will control how many books show per page and which page to show.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL
);

INSERT INTO books (title) VALUES
('The Hobbit'),
('1984'),
('Pride and Prejudice'),
('To Kill a Mockingbird'),
('The Great Gatsby'),
('Moby Dick');

-- Set page_size and page_number variables
-- Your code here

Hint

Use SET @variable_name = value; to create variables in MySQL.

3

Calculate OFFSET for pagination

Create a variable offset that calculates the starting row for the page. Use the formula: (page_number - 1) * page_size. Use SET to assign this to @offset.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL
);

INSERT INTO books (title) VALUES
('The Hobbit'),
('1984'),
('Pride and Prejudice'),
('To Kill a Mockingbird'),
('The Great Gatsby'),
('Moby Dick');

SET @page_size = 3;
SET @page_number = 2;

-- Calculate offset
-- Your code here

Hint

Use parentheses to ensure correct order of operations in the calculation.

4

Write the paginated SELECT query

Write a SQL query that selects id and title from books. Use LIMIT with @page_size and OFFSET with @offset to get the correct page of books.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL
);

INSERT INTO books (title) VALUES
('The Hobbit'),
('1984'),
('Pride and Prejudice'),
('To Kill a Mockingbird'),
('The Great Gatsby'),
('Moby Dick');

SET @page_size = 3;
SET @page_number = 2;

SET @offset = (@page_number - 1) * @page_size;

-- Write the SELECT query with LIMIT and OFFSET
-- Your code here

Hint

Use LIMIT and OFFSET together to get the correct page.

Practice

1. What does the LIMIT clause do in a MySQL query?

easy

A. It controls how many rows the query returns.

B. It controls the order of the rows returned.

C. It filters rows based on a condition.

D. It changes the database schema.

Solution

1. Step 1: Understand the purpose of LIMIT

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

2. Step 2: Compare with other options

   Ordering is done by ORDER BY, filtering by WHERE, and schema changes by DDL commands, not LIMIT.

3. Final Answer:

   It controls how many rows the query returns. -> Option A

4. Quick Check:

   LIMIT = number of rows [OK]

Hint: LIMIT sets max rows returned, like page size [OK]

Common Mistakes:

• Confusing LIMIT with ORDER BY
• Thinking LIMIT filters rows by condition
• Assuming LIMIT changes table structure

2. Which of the following is the correct syntax using the OFFSET keyword to get 10 rows starting from the 7th row in MySQL?

easy

A. SELECT * FROM table LIMIT 6, 10;

B. SELECT * FROM table LIMIT 10 OFFSET 6;

C. SELECT * FROM table OFFSET 6 LIMIT 10;

D. SELECT * FROM table LIMIT 10, 6;

Solution

1. Step 1: Understand LIMIT and OFFSET syntax

   MySQL supports both LIMIT count OFFSET offset and LIMIT offset, count syntax.

2. Step 2: Identify correct syntax for 10 rows starting at 7th row

   OFFSET 6 means skip first 6 rows, then LIMIT 10 means get next 10 rows. SELECT * FROM table LIMIT 10 OFFSET 6; uses this correctly.

3. Final Answer:

   SELECT * FROM table LIMIT 10 OFFSET 6; -> Option B

4. Quick Check:

   LIMIT count OFFSET offset = correct syntax [OK]

Hint: Use LIMIT count OFFSET offset for clarity [OK]

Common Mistakes:

• Swapping LIMIT and OFFSET values
• Using OFFSET before LIMIT incorrectly
• Confusing LIMIT offset,count with LIMIT count OFFSET offset

3. Given the table users with rows numbered by id from 1 to 20, what rows will this query return?
SELECT * FROM users ORDER BY id ASC LIMIT 5 OFFSET 10;

medium

A. Rows with id 1 to 5

B. Rows with id 6 to 10

C. Rows with id 11 to 15

D. Rows with id 16 to 20

Solution

1. Step 1: Understand OFFSET and LIMIT effect on rows

   OFFSET 10 skips first 10 rows (id 1 to 10), LIMIT 5 returns next 5 rows.

2. Step 2: Identify which rows are returned

   After skipping 10 rows, rows with id 11 to 15 are returned.

3. Final Answer:

   Rows with id 11 to 15 -> Option C

4. Quick Check:

   OFFSET 10 + LIMIT 5 = rows 11-15 [OK]

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

Common Mistakes:

• Counting OFFSET as inclusive row
• Mixing up OFFSET and LIMIT order
• Assuming OFFSET starts at 1 instead of 0

4. Identify the error in this query intended to get 5 rows starting from the 3rd row:
SELECT * FROM products LIMIT OFFSET 3, 5;

medium

A. Syntax error: LIMIT should come before OFFSET without comma.

B. OFFSET cannot be used with LIMIT in this way.

C. No error; query is correct.

D. LIMIT and OFFSET are reversed in order.

Solution

1. Step 1: Check correct syntax for LIMIT and OFFSET

   Correct syntax is either LIMIT count OFFSET offset or LIMIT offset, count without the word OFFSET in the second form.

2. Step 2: Identify error in given query

   The query uses both LIMIT and OFFSET keywords together incorrectly with a comma, causing syntax error.

3. Final Answer:

   Syntax error: LIMIT should come before OFFSET without comma. -> Option A

4. Quick Check:

   LIMIT offset,count or LIMIT count OFFSET offset [OK]

Hint: Use either LIMIT offset,count or LIMIT count OFFSET offset [OK]

Common Mistakes:

• Mixing LIMIT and OFFSET keywords with commas
• Using OFFSET before LIMIT
• Adding OFFSET keyword with comma syntax

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

hard

A. SELECT * FROM products ORDER BY created_at DESC LIMIT 45 OFFSET 15;

B. SELECT * FROM products ORDER BY created_at DESC LIMIT 60 OFFSET 15;

C. SELECT * FROM products ORDER BY created_at DESC LIMIT 15, 45;

D. SELECT * FROM products ORDER BY created_at DESC LIMIT 15 OFFSET 45;

Solution

1. Step 1: Calculate OFFSET for page 4 with 15 items per page

   Page 1 shows rows 0-14, page 2 rows 15-29, page 3 rows 30-44, so page 4 starts at offset 45.

2. Step 2: Write query with LIMIT and OFFSET

   LIMIT 15 means 15 rows per page, OFFSET 45 skips first 45 rows. SELECT * FROM products ORDER BY created_at DESC LIMIT 15 OFFSET 45; matches this.

3. Final Answer:

   SELECT * FROM products ORDER BY created_at DESC LIMIT 15 OFFSET 45; -> Option D

4. Quick Check:

   OFFSET = (page-1)*page_size = 45 [OK]

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

Common Mistakes:

• Swapping LIMIT and OFFSET values
• Calculating OFFSET incorrectly
• Using LIMIT as total rows instead of per page