Using LIMIT and OFFSET in SQL

Table of Contents

1. Introduction
2. What is the LIMIT Clause?
3. Syntax of LIMIT
4. Why Use LIMIT?
5. Basic Example of LIMIT
6. What is the OFFSET Clause?
7. Syntax of LIMIT with OFFSET
8. Practical Pagination with LIMIT and OFFSET
9. Using ORDER BY with LIMIT
10. Retrieving Top-N Records
11. Pagination UI and SQL Integration
12. MySQL vs PostgreSQL vs SQLite: Syntax Notes
13. Common Errors with LIMIT and OFFSET
14. LIMIT with Joins
15. LIMIT in Subqueries
16. Performance Tips
17. Real-World Use Cases
18. Best Practices
19. Summary Table of Syntax Patterns
20. Summary and What’s Next

1. Introduction

In SQL, the result of a query can return thousands of records — but often, we only need a subset. The LIMIT and OFFSET clauses let you control how many rows you get and where to start reading from. This is essential for building pagination, leaderboards, and top-ranked reports.

2. What is the LIMIT Clause?

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

3. Syntax of LIMIT

sqlCopyEditSELECT column1, column2
FROM table_name
LIMIT number_of_rows;

Example:

sqlCopyEditSELECT * FROM products
LIMIT 10;

This returns only the first 10 rows of the products table.

4. Why Use LIMIT?

• Prevents excessive data retrieval
• Speeds up query performance
• Useful in pagination, previews, summaries
• Reduces network and UI load

5. Basic Example of LIMIT

sqlCopyEditSELECT name, price FROM products
ORDER BY price DESC
LIMIT 5;

This returns the 5 most expensive products.

6. What is the OFFSET Clause?

OFFSET skips a specified number of rows before starting to return rows.

7. Syntax of LIMIT with OFFSET

sqlCopyEditSELECT column1, column2
FROM table_name
ORDER BY column
LIMIT number_of_rows OFFSET number_to_skip;

Example:

sqlCopyEditSELECT name FROM students
ORDER BY marks DESC
LIMIT 10 OFFSET 10;

This retrieves records 11 to 20 in descending marks order.

8. Practical Pagination with LIMIT and OFFSET

For page 1 of 10 items per page:

sqlCopyEditSELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 0;

For page 2:

sqlCopyEditLIMIT 10 OFFSET 10;

For page N:

sqlCopyEditLIMIT 10 OFFSET (N - 1) * 10;

Use in web apps to handle pagination efficiently.

9. Using ORDER BY with LIMIT

Always combine LIMIT with ORDER BY to get predictable, meaningful results:

sqlCopyEditSELECT * FROM orders
ORDER BY order_date DESC
LIMIT 5;

Without ORDER BY, the order of rows is not guaranteed.

10. Retrieving Top-N Records

sqlCopyEditSELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 3;

Get top 3 highest-paid employees.

11. Pagination UI and SQL Integration

Works with frontend frameworks (React, Angular, etc.) via API calls that support page numbers.

12. MySQL vs PostgreSQL vs SQLite: Syntax Notes

You can also use this compact format (PostgreSQL, SQLite):

sqlCopyEditSELECT * FROM table
LIMIT 10, 20;  -- Fetch 20 rows, starting from offset 10 (MySQL only)

13. Common Errors with LIMIT and OFFSET

• Using LIMIT without ORDER BY (leads to random results)
• Incorrect offset math in pagination
• Forgetting that OFFSET starts from 0
• Overusing high OFFSET values can degrade performance

14. LIMIT with Joins

sqlCopyEditSELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
ORDER BY o.total DESC
LIMIT 10;

LIMIT applies after the join result is computed.

15. LIMIT in Subqueries

sqlCopyEditSELECT * FROM (
  SELECT * FROM products
  ORDER BY price DESC
  LIMIT 5
) AS top_products
ORDER BY price ASC;

Use subqueries when needing multiple sorted stages.

16. Performance Tips

• Avoid high OFFSET values on large datasets
• Consider using key-based pagination (e.g., WHERE id > last_seen_id)
• Add indexes on columns used in ORDER BY

17. Real-World Use Cases

• Product catalog pagination
• Paginating blog posts or search results
• Top N performers in a leaderboard
• Infinite scrolling in modern web apps

18. Best Practices

• Always use ORDER BY with LIMIT
• Validate page number to prevent invalid offsets
• Use indexes to support sorted fields
• For large datasets, consider cursor-based or seek-based pagination instead of OFFSET

19. Summary Table of Syntax Patterns

20. Summary and What’s Next

The LIMIT and OFFSET clauses are essential tools for managing how much data is returned from a query. They power everything from pagination to performance optimization.