LIMIT and OFFSET in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this ORDER BY and Result Sorting
What are LIMIT and OFFSET in SQL?
LIMIT caps how many rows a query returns.
By itself that sounds simple. The power shows up when you pair it with ORDER BY. Order your rows by something meaningful — price, date, score — and LIMIT gives you the actual top or bottom of your dataset. Not a random sample, not an arbitrary set of rows: the specific ones that rank highest or lowest by the metric you care about.
This is an everyday analyst task. Which five customers joined most recently? What are the three most expensive products? Who are the top ten earners this quarter? All of these are just ORDER BY plus LIMIT. Write the sort that defines "top" or "most recent" or "most expensive," then cap the result at the number you need.
How do you return the top N rows in SQL?
To get the three cheapest products:
SELECT id, name, price
FROM products
ORDER BY price
LIMIT 3SQL sorts all products by price, then stops after the first three. The rest of the rows are never returned.
How do you paginate results with LIMIT and OFFSET?
OFFSET pairs with LIMIT for pagination. It skips a number of rows before the count starts. To get products ranked 6th through 10th by price:
SELECT id, name, price FROM products ORDER BY price LIMIT 5 OFFSET 5
OFFSET 5 skips the five cheapest. LIMIT 5 returns the next five. Page 1 of a paginated list is OFFSET 0, page 2 is OFFSET 10, page 3 is OFFSET 20.
OFFSET without LIMIT is valid — it skips N rows and returns everything after. LIMIT without OFFSET returns the first N rows.
Why does LIMIT return different rows each run?
The one thing that trips people up: LIMIT without ORDER BY gives you unpredictable results.
Two runs of the same query can return different rows. SQL isn't broken — it's being honest about the fact that without a sort, "which rows come first" has no meaningful answer. Any time LIMIT is doing real work, ORDER BY needs to be there too.
You want the 5 most recently hired employees. Which query gives you a reliable answer?
Practice LIMIT and OFFSET in SQL
Brightlane's customer success team is onboarding a new account manager who wants to review the most recent sign-ups first.
Write a query to return the ID, name, and email of the 5 most recently registered customers.
Assumptions:
- The
customerstable contains every customer Brightlane has on file. - The
created_atcolumn records when each customer registered.
Output:
- One row per customer, with columns
id,name, andemail, sorted bycreated_atdescending and capped at 5 rows.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 LIMIT and OFFSET practice problems
Write a query to return the ID, name, and email of the 5 most recently registered customers.
Write a query to return the ID, name, and price of the 3 cheapest products.
Write a query to return the ID, name, and hire date of the 4 most recently hired employees.
Write a query to return the ID, name, and email for the 10 customers on that page.
Write a query to return the ID, name, and price of those 5 products.
Write a query to return the ID, name, and email of the accounts on that page.
Write a query to return the ID and name of every product after the first 5.
Write a query to return the ID, name, and price for every product in that batch.
Write a query to return the ID, name, and price for every product in that batch.
Start learning to practice all 9 LIMIT and OFFSET problems, with instant grading and mastery tracking.
Common questions about LIMIT and OFFSET
What happens if LIMIT is larger than the number of rows?
You get every row and no error. LIMIT sets a ceiling, not a target, so asking for a hundred thousand rows from a table holding sixty-three returns the sixty-three. Nothing is padded and nothing complains.
Can you use OFFSET without LIMIT?
Yes. OFFSET on its own skips the rows you name and returns everything after them, which is how you ask for the tail of a result. LIMIT on its own is the more common half of the pair.
Is LIMIT 0 valid SQL?
Yes. It returns no rows while still running the query, which makes it a cheap way to check that a statement parses and that the column names and types are what you expected before you run it for real.
Does LIMIT change which rows get sorted?
No. ORDER BY applies to the whole result and LIMIT then cuts the front off it, which is why the pair gives you a genuine top ten rather than ten arbitrary rows sorted after the fact.
