How to Use LIMIT and OFFSET in SQL: A Beginner’s Guide

When you're working with large datasets in SQL, you often don’t want to retrieve every row—especially if you're building a dashboard, pagination system, or reviewing data samples. That’s where the LIMIT and OFFSET clauses come in.

These clauses allow you to control how much data your query returns, which is useful for performance, readability, and front-end integration.

In this guide, you’ll learn how LIMIT and OFFSET work, how to use them together, and how to avoid common mistakes.

What Does LIMIT Do in SQL?

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

Basic Example:

SELECT * FROM users LIMIT 10;


This query returns only the first 10 rows from the users table.

When to Use LIMIT:

• Preview a sample of your data.
• Display top results (e.g., top 10 customers).
• Build pagination for web applications.

You can try examples like this instantly in the Galaxy SQL Editor.

What Does OFFSET Do in SQL?

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

Example:

SELECT * FROM users OFFSET 5;


This query skips the first 5 rows and returns everything after.

By itself, OFFSET isn’t very useful. But combined with LIMIT, it becomes a powerful tool for pagination.

LIMIT + OFFSET for Pagination

The most common use case for combining LIMIT and OFFSET is paginated results—for example, returning results 11 through 20.

Example:

SELECT * FROM users LIMIT 10 OFFSET 10;


This returns rows 11–20 from the users table.

Use this pattern in any app or dashboard where you want to control how much data loads at once.

Want to see it in action? Visit our Top Data Jobs page to see sorted and limited listings in a real interface.

Sorting Your Results First

If you use LIMIT without ORDER BY, the “first N rows” may be arbitrary.

Good Practice:

SELECT * FROM users ORDER BY created_at DESC LIMIT 10;


This ensures you get the most recently created users.

For more on sorting and organizing results, check out our guide to ORDER BY vs GROUP BY.

Use Cases for LIMIT and OFFSET

1. Sample Preview

See a quick subset of data:

SELECT * FROM products LIMIT 5;


2. Infinite Scroll or Pagination

Load data in chunks as users scroll or flip through pages:

SELECT * FROM reviews ORDER BY created_at DESC LIMIT 10 OFFSET 30;


3. Export Batching

Export large datasets in smaller pieces (e.g., 1,000 rows at a time).

Best Practices

• Always pair LIMIT with ORDER BY to control which records are returned.
• Use indexes on your ORDER BY column to keep pagination fast.
• Avoid high OFFSET values on massive tables—they can slow down query performance.
• Consider using keyset pagination (WHERE id > ?) for better scalability at large page numbers (source).

Common Pitfalls

1. Missing ORDER BY

SELECT * FROM users LIMIT 10;


This might return different results every time. Always specify sort order.

2. High OFFSETs Can Be Slow

SELECT * FROM logs ORDER BY timestamp DESC LIMIT 100 OFFSET 100000;


This can cause performance issues on large tables. Look into indexed pagination alternatives for large-scale use cases.

3. OFFSET Starts at 0

If you want the first page, use OFFSET 0, not OFFSET 1.

Final Thoughts

LIMIT and OFFSET are essential tools for working with big datasets, especially when you need fast previews, clean exports, or paginated UI results.

They're easy to learn but critical to use correctly—especially when performance and consistency matter.

Practice these concepts in the Galaxy SQL Editor and see how data slicing works in real time.

Continue learning: