Course contentsShow
← Computer Science
Lesson 1569 of 2,87236. Database FundamentalsFree lesson

LIMIT and Result Set Pagination

Restricting the number of rows returned and implementing basic pagination with LIMIT and OFFSET.

LIMIT and Result Set Pagination

What you'll learn: How to control the number of rows returned by a query and implement basic pagination using LIMIT and OFFSET.

The Core Idea

When querying a database, you often don't want all the results at once—especially when a table contains thousands or millions of rows. The LIMIT clause restricts how many rows are returned, while OFFSET lets you skip a certain number of rows before starting to return results. Together, they enable pagination: breaking large result sets into manageable "pages."

Think of it like reading a book. You don't read all 500 pages at once; you read page 1, then page 2, and so on. LIMIT is like saying "show me 10 lines," and OFFSET is like saying "but start from line 20."

Basic Syntax

SELECT * FROM users
ORDER BY username
LIMIT 10;

This returns only the first 10 users (when sorted by username).

Adding Pagination with OFFSET

SELECT * FROM users
ORDER BY username
LIMIT 10 OFFSET 20;

This skips the first 20 users and returns the next 10—effectively giving you "page 3" if each page shows 10 users (page 1: rows 0-9, page 2: rows 10-19, page 3: rows 20-29).

Why ORDER BY Matters

Without ORDER BY, the database can return rows in any order, making pagination unpredictable. Always sort your results before paginating to ensure consistent "pages."

Calculating Offset for a Given Page

For page number P with N results per page:

  • LIMIT N
  • OFFSET (P - 1) * N

Key Takeaway: Use LIMIT to restrict result size and combine it with OFFSET to implement pagination, always with ORDER BY for consistent results.