Limiting Results with LIMIT

Explanation

Sometimes a table has thousands of rows, but you only want to look at a handful — the first few, the most recent few, or just a quick sample. LIMIT caps the number of rows a query returns.

Imagine the orders table in the store dataset has 5,000 rows. Running SELECT * FROM orders would return all 5,000. Adding LIMIT 10 returns just the first 10 rows the database gives you.

Syntax

SELECT column1, column2
FROM table_name
LIMIT n;

To skip past some rows before returning results, add OFFSET:

SELECT column1, column2
FROM table_name
LIMIT n OFFSET m;

This skips the first m rows, then returns up to n rows after that. OFFSET is often used for paging through results — for example, showing results 11-20 with LIMIT 10 OFFSET 10.

Interactive Example

Common Mistakes

• Using LIMIT without ORDER BY when order matters. Without sorting first, LIMIT 5 just gives you five rows in whatever order the database happens to return them, which may not be the five you actually want.
• Confusing the order of LIMIT and OFFSET. The correct syntax is LIMIT n OFFSET m, meaning "return up to n rows, after skipping m."
• Forgetting LIMIT entirely on exploratory queries. When you're just peeking at a large table, LIMIT 10 saves you from scrolling through thousands of rows you don't need to see yet.

Best Practices

• Add LIMIT when exploring an unfamiliar or large table, so you're not overwhelmed by results.
• Pair LIMIT with ORDER BY whenever you want a specific subset, like "top 5" or "most recent 10," rather than an arbitrary one.
• Use OFFSET together with LIMIT for paging, but keep in mind that on very large tables, high offsets can be slower since the database still has to skip past all those earlier rows.

Practice Question

Using the first playground above, write a query against the orders table that returns the 3 most recent orders (hint: order by order_date descending and limit to 3 rows).

Summary

LIMIT restricts how many rows a query returns, which is useful both for exploring large tables and for building features like "show the top 5." OFFSET lets you skip a number of rows first, which is handy for paging through results in chunks.

FAQ