How to Use LIMIT in SQL

Use LIMIT when you need only the first few rows of a result set instead of every match. This is essential for pagination, previews, top results, and performance-friendly sampling.

What you’ll build or solve

You’ll learn how to use LIMIT in SQL to restrict result size. You’ll also know how it works with sorting and pagination.

When this approach works best

This approach is the right choice when full query results are unnecessary.

Common real-world scenarios include:

• Latest 10 orders
• Top 5 products
• Search result pagination
• Dashboard previews
• Query debugging samples

This is a bad idea when the result must include every matching row for analytics or exports.

Prerequisites

You only need:

• A SQL table with rows
• Basic SELECT and ORDER BY knowledge

Step-by-step instructions

Step 1: Add LIMIT at the end of the query

Place LIMIT after filtering and sorting.

SELECT *
FROM orders
LIMIT 10;

This returns only the first 10 rows.

It becomes especially useful with ORDER BY.

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10;

This gives the latest 10 orders.

Use offset pagination when needed.

SELECT *
FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

This skips the first 40 rows and returns the next 20.

What to look for:

• LIMIT restricts row count
• Best used after ORDER BY
• Great for previews and pagination
• OFFSET skips earlier rows
• Without sorting, returned rows may be arbitrary

Examples you can copy

Latest 5 users

SELECT *
FROM users
ORDER BY created_at DESC
LIMIT 5;

Top products

SELECT *
FROM products
ORDER BY sales DESC
LIMIT 3;

Pagination page 3

SELECT *
FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

Common mistakes and how to fix them

Mistake 1: Using LIMIT without sorting

What the reader might do:

SELECT *
FROM orders
LIMIT 10;

Why it breaks: the selected rows may not be the most useful or consistent.

Corrected approach:

Add ORDER BY.

Mistake 2: Wrong offset math

What the reader might do:

LIMIT 10 OFFSET 10

Why it breaks: this returns page 2, not page 1.

Corrected approach:

Use (page - 1) * page_size.

Mistake 3: Using LIMIT for one top row without sorting

What the reader might do:

SELECT *
FROM products
LIMIT 1;

Why it breaks: this returns an arbitrary row.

Corrected approach:

Sort first by the ranking dimension.

Troubleshooting

If the result changes unpredictably, add ORDER BY.

If pagination skips wrong rows, recalculate the offset.

If only one best row is needed, combine sorting with LIMIT 1.

If performance slows on deep pages, consider keyset pagination.

Quick recap

• Use LIMIT to restrict result size
• Add ORDER BY for stable top rows
• Use OFFSET for pagination
• Calculate offset carefully
• Great for previews and top results