MediumSQL#256 min readJul 11, 2026
LIMIT
practice
Practice modeTest yourself instead of reading straight through
Hook: LIMIT is the restaurant host for your result set: it decides how many rows actually get served, which is why interviewers use it to test whether you understand ordering and pagination, not just syntax.
Question: What does LIMIT do in SQL?
Answer: LIMIT caps the number of rows returned by a query. It is usually paired with ORDER BY so the subset is predictable, and with OFFSET when you want paging. Without ORDER BY, LIMIT still works, but the rows you get are not guaranteed to be the same each time.
Interview-Ready Answer: "I use LIMIT to cap the number of rows a query returns. If I want a stable result, I always add ORDER BY, because LIMIT alone does not define which rows are chosen. For paging, I combine it with OFFSET, and I also remember that LIMIT is dialect-specific—standard SQL often uses FETCH FIRST instead."
%22%20stroke%3D%22%234f46e5%22%20stroke-width%3D%221.5%22%2F%3E%3Ctext%20x%3D%22500%22%20y%3D%22308%22%20text-anchor%3D%22middle%22%20font-size%3D%2215%22%20font-weight%3D%22bold%22%20fill%3D%22%23ffffff%22%20font-family%3D%22Segoe%20UI%2C%20Arial%2C%20sans-serif%22%3ELIMIT%20clause%3C%2Ftext%3E%3C%2Fsvg%3E)
What LIMIT is
Detailed Explanation: LIMIT is a clause that tells the database the maximum number of rows to return. Think of it as a row cap, not a row filter: it does not decide which rows qualify, only how many of the qualifying rows you see.
How it works under the hood
- The database first figures out the rest of the query: joins,
WHERE, grouping, and sorting. - Conceptually, SQL processing happens in this order:
FROM/JOIN→WHERE→GROUP BY→HAVING→ORDER BY→LIMIT/OFFSET. - The
ORDER BYstep matters because LIMIT takes the first N rows of the ordered result. If there is no order, the database may return any matching rows. - With LIMIT alone, many engines can stop reading once they have produced enough rows. This is an important performance win for small top-N queries.
- If
OFFSETis present, the database skips the first M rows and then returns the next N rows. That makes deep pages slower because the skipped rows still have to be found or counted. - A good optimizer can sometimes use a top-N optimization (an efficient plan that finds only the best N rows) or an index that already matches the sort order, so it does not sort the whole table.
LIMIT vs related clauses
| Clause | What it does | When it acts | Good to know |
|---|---|---|---|
| LIMIT | Caps rows | After sort | Dialect-specific |
| WHERE | Filters rows | Before sort | Changes which rows qualify |
| ORDER BY | Sorts rows | Before LIMIT | Needed for stable paging |
| FETCH FIRST | Standard cap | After sort | SQL:2008+ syntax |
| TOP | Caps rows | Query start | Common in SQL Server |
When and why to use it: use LIMIT for previews, dashboards, search results, API responses, and any top-N report. It is also useful in debugging when you want a tiny sample, and LIMIT 0 is a neat trick when you want the column list without any data.
Performance and complexity: if the engine must sort many matching rows, the cost can be roughly O(R log R) time and O(R) memory for R candidate rows. If an index already provides the needed order, the engine may read only the first N rows, which feels close to O(N). Deep pagination is the classic trap: asking for page 1000 with page size 20 means skipping 19,980 rows, which can become noticeably slow.
- Important edge case:
LIMIT 0is valid and returns no rows. - Important edge case:
LIMITwithoutORDER BYis legal but not deterministic. - Important edge case: if several rows tie on the sort key, add a unique tiebreaker such as an ID so pagination does not jump around.
- Dialect note: MySQL, PostgreSQL, and SQLite support
LIMIT; SQL Server usually usesTOPorOFFSET ... FETCH; standard SQL commonly usesFETCH FIRST n ROWS ONLY.
Real-World Example: In an e-commerce product listing service, the API might return 20 items per page with ORDER BY created_at DESC, product_id DESC LIMIT 20 OFFSET 40. That gives the frontend page 3 of a catalog, and the extra product ID in the order clause keeps the page stable when two products share the same timestamp.
What goes wrong when LIMIT is misunderstood? A team forgets ORDER BY, ships a “top products” endpoint, and users start seeing duplicates on page 2 and missing items on page 3. The symptom is subtle: the same request parameters return different product IDs across deployments or even across repeated requests, support tickets mention “items moved around,” and logs show the query was fast but inconsistent. The bug is not LIMIT itself; it is using LIMIT without a deterministic order.
SQL
-- LIMIT demo: predictable top-N, pagination, and edge cases
-- This script is intentionally simple and works in SQLite, PostgreSQL, and MySQL 8+.
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer TEXT NOT NULL,
status TEXT NOT NULL,
created_at TEXT NOT NULL,
total_cents INTEGER NOT NULL
);
INSERT INTO orders (order_id, customer, status, created_at, total_cents) VALUES
(1, 'Ava', 'PAID', '2026-07-01 09:15:00', 2599),
(2, 'Ben', 'PAID', '2026-07-01 09:20:00', 1899),
(3, 'Cara', 'CANCELLED', '2026-07-01 09:25:00', 4999),
(4, 'Dan', 'PAID', '2026-07-01 09:30:00', 1299),
(5, 'Eli', 'PAID', '2026-07-01 09:35:00', 3999);
-- Best practice: LIMIT + ORDER BY gives a predictable top-N result.
SELECT order_id, customer, total_cents
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC, order_id DESC
LIMIT 3;
-- Pagination: skip the first 2 matching rows, then return the next 2.
SELECT order_id, customer, total_cents
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC, order_id DESC
LIMIT 2 OFFSET 2;
-- Edge case: LIMIT 0 is valid and returns no rows.
-- Useful when you only want to validate the shape of a query.
SELECT order_id, customer
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC
LIMIT 0;
-- Gotcha demo: this is legal, but the chosen rows are not guaranteed.
-- Without ORDER BY, the database may pick any 2 matching rows.
SELECT order_id, customer
FROM orders
WHERE status = 'PAID'
LIMIT 2;
-- Another edge case: OFFSET beyond the result size returns an empty set.
SELECT order_id, customer
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC, order_id DESC
LIMIT 2 OFFSET 10;Follow-up & Tricky Questions:
- How does LIMIT interact with ORDER BY? ORDER BY comes first in the logical result, and LIMIT trims that sorted list. If you leave out ORDER BY, LIMIT still works, but the selected rows are not guaranteed.
- What is OFFSET for? OFFSET skips a number of rows before LIMIT starts counting. It is useful for page 2, page 3, and so on, but large offsets get slower because the database still has to walk past the skipped rows.
- Is LIMIT standard SQL? No, LIMIT is common in PostgreSQL, MySQL, and SQLite, but standard SQL uses
FETCH FIRST n ROWS ONLY. SQL Server usually usesTOPorOFFSET ... FETCH. - Can LIMIT be used with GROUP BY? Yes, because it applies after grouping and sorting. That means you can get the top 10 grouped results, such as the 10 highest-selling products.
- How do I make pagination stable? Sort by a non-changing key and add a unique tiebreaker, such as
created_at DESC, id DESC. This prevents rows with equal timestamps from jumping between pages. - Tricky: Does LIMIT 10 OFFSET 0 mean something different from LIMIT 10? Functionally, no; OFFSET 0 skips nothing. It is just more verbose.
- Tricky: Does LIMIT reduce the work of WHERE? No, WHERE still determines which rows qualify first. LIMIT only shortens the final output, and the engine may still scan a lot of data to find the first N matches.
- Tricky: Can LIMIT change query meaning without ORDER BY? Yes, because it can hide nondeterminism. The query is legal, but the specific rows you see may vary across runs, indexes, or plans.
Common Mistakes:
- Using LIMIT without ORDER BY. Correction: add a deterministic sort key whenever the exact rows matter.
- Thinking LIMIT filters data. Correction: use WHERE to decide which rows qualify; use LIMIT only to cap how many are returned.
- Ignoring deep OFFSET costs. Correction: for large pages, prefer keyset pagination such as “give me rows after this last seen ID.”
- Forgetting dialect differences. Correction: know that LIMIT is not universal; standard SQL often uses FETCH FIRST, and SQL Server commonly uses TOP.
Memory Hook: “LIMIT is the bouncer; ORDER BY is the guest list.” LIMIT decides how many people get in, but ORDER BY decides who stands at the front of the line.
Cheat Sheet:
- LIMIT caps rows returned.
- ORDER BY makes LIMIT predictable.
- OFFSET skips rows for paging.
- LIMIT 0 returns an empty result.
- Deep OFFSET can be slow.
- Standard SQL often uses FETCH FIRST.
Practice Tasks:
- Write a query that returns the 5 newest orders, with a stable tie breaker.
- Change the example to fetch page 4 with 10 rows per page.
- Rewrite the pagination query using keyset pagination instead of OFFSET.