SR

LIMIT & OFFSET

Cap the number of rows returned — and paginate through results

Beginner 8 minLIMITOFFSETpagination

Limiting Results

LIMIT caps how many rows your query returns. LIMIT 10 gives you at most 10 rows, even from a table with millions of records. Combined with ORDER BY, it becomes the foundation for top-N queries. Here's what to know:

Top-N queries — ORDER BY price DESC LIMIT 5 gives you the 5 most expensive items. Without ORDER BY first, LIMIT just returns 5 arbitrary rows — almost never what you actually want.
OFFSET skips rows — LIMIT 10 OFFSET 20 skips the first 20 rows and returns rows 21 through 30. This is the foundation of pagination — showing results page by page in web applications and APIs.
LIMIT without ORDER BY is random — LIMIT 5 alone gives you 5 arbitrary rows with no guaranteed order. Always combine LIMIT with ORDER BY when specific rows matter to your result.
Dialect differences — LIMIT is standard in SQLite, PostgreSQL, and MySQL. SQL Server uses SELECT TOP 10 ... or OFFSET n ROWS FETCH NEXT m ROWS ONLY. Oracle (12c+) and ANSI SQL use FETCH FIRST 10 ROWS ONLY. The concept is identical — only the syntax varies.
Knowledge Canvas

How LIMIT Works

Cap the number of rows returned

  • LIMIT runs after ORDER BY — last in execution order
  • LIMIT n returns at most n rows
  • OFFSET skips rows before returning: pagination
  • Without ORDER BY, which rows you get is unpredictable

LIMIT Gotchas

Watch out for these

  • LIMIT without ORDER BY = random subset each time
  • OFFSET scales poorly — row 1,000,000 still reads 999,999 rows
  • Syntax varies: MySQL LIMIT, SQL Server TOP, Oracle FETCH FIRST
  • LIMIT 0 returns zero rows but still validates the query

Common Patterns

Where LIMIT shines

  • Top-N: ORDER BY revenue DESC LIMIT 10
  • Pagination: LIMIT 20 OFFSET 40 (page 3)
  • Sampling: ORDER BY RANDOM() LIMIT 5
  • Existence check: LIMIT 1 for quick lookups

LIMIT Across Databases

Syntax differences to know

MySQL / SQLite / PostgreSQL
LIMIT n OFFSET m
SQL Server
TOP n (or OFFSET FETCH)
Oracle
FETCH FIRST n ROWS ONLY
Standard SQL
FETCH FIRST / OFFSET
Syntax
Syntax Template
1SELECT
2 columns
3FROM table
4ORDER BY
5 column
6LIMIT count
7OFFSET skip;
LIMIT nReturn at most n rows
OFFSET nSkip the first n rows before returning
Sample Data
products
10 rows
nameprice
Wireless Mouse24.99
Office Desk189
LED Desk Lamp45.5
USB-C Cable12.99
Bluetooth Speaker79
Office Chair95
Coffee Maker89.99
Notebook Set8.5
Wireless Headphones159
Standing Desk425

Worked Example

Show the 3 most expensive products.

SQL
1SELECT
2 name,
3 price
4FROM products
5ORDER BY
6 price DESC
7LIMIT 3;
First we sort all products by price descending. Then LIMIT 3 returns only the first three rows from that sorted list — the three most expensive products.
Output
3 rows
nameprice
Standing Desk425
Office Desk189
Wireless Headphones159

Key Concepts

1LIMIT without ORDER BY gives you random rows
2OFFSET starts counting from 0 — OFFSET 20 skips rows 1–20
3Page N of size S: LIMIT S OFFSET (N-1)*S
4Some databases use TOP or FETCH FIRST instead of LIMIT

Pro Tip

Large tables return millions of rows. LIMIT keeps results manageable and makes top-N queries possible.

When to Use

Paginated web pages, "Top 10" lists, previewing table contents, sampling data during development.

Challenge

Solve the problem below

Find the 3 cheapest products. Show their name and price.

Your Query

Previous

ORDER BY

Next

Working with NULL