What does LIMIT do to a query's results?
Choose an answer, then check it.
Databases & SQL · Foundations
LIMIT
On this page 9 sections
In 30 seconds
LIMIT caps a query's result: it returns only the first N rows The N rows that come first in a query's result, which is exactly what LIMIT N returns. Full entry →, no matter how many rows the table holds. SQLite puts the working definition precisely — the SELECT returns the first N rows of its result set The rows and columns a SELECT query returns, shaped like a small table; LIMIT trims it to the first N rows. Full entry → only — and W3Schools says the same job in plainer words: limit the number of records to return. The shape is simple: LIMIT N goes at the end of a query, as in SELECT * FROM products LIMIT 5;. Pair it with ORDER BY for the first N of a sorted list. LIMIT changes nothing; it only trims what comes back.
Why this matters
Without LIMIT, every query dumps the whole table at you: a million-row log returns a million rows. That is slow to transfer, slow to read, and pointless when you only need a look. LIMIT is the small clause that fixes it — it keeps queries fast and screens readable, and W3Schools notes that returning a large number of records can impact performance. It also powers pagination Showing the results of a query in pages, using LIMIT to size each page and OFFSET to move to the next one. Full entry →, the pages of results behind every product list and search screen. And because LIMIT never changes data, it is a safe clause for beginners to experiment with. One keyword, but it sits on every list, every dashboard, and every report.
The college version
The working definition: the first N rows
Every source documents the same job. SQLite's SELECT reference states that the SELECT returns the first N rows of its result set only, where N is the value the LIMIT expression evaluates to. W3Schools describes the same operation in plainer words: the SELECT TOP clause SQL Server's name for the same operation as LIMIT: it caps the rows a query returns at a specified number. Full entry → is used to limit the number of records to return, and the MySQL syntax it shows reaches the same goal with LIMIT. PostgreSQL's documentation sums up the whole family: LIMIT and OFFSET The clause paired with LIMIT that skips a set number of rows before the capped result begins. Full entry → allow you to retrieve just a portion of the rows that are generated by the rest of the query. Together, those descriptions give a short working definition: LIMIT takes the first N rows of a query's result and returns nothing beyond them. The rest of the table still exists; the query simply does not carry it back.
The basic shape: LIMIT N at the end
LIMIT has the simplest syntax in SQL: write the number at the end of the query. PostgreSQL documents the shape as SELECT select_list FROM table_expression [ ORDER BY ... ] [ LIMIT { count | ALL } ], and every example in this lesson follows it. The original query SELECT * FROM products LIMIT 5; asks for all columns of the products table and returns only the first five rows. The number after LIMIT is a cap, not a promise: PostgreSQL notes that no more than that many rows will be returned, but possibly fewer, if the query itself yields fewer rows. A table with three products answers LIMIT 5 with three rows, not five. And LIMIT ALL, which PostgreSQL documents as the same as omitting the clause, is rarely needed — no cap at all is the default.
LIMIT with ORDER BY: the classic pairing
On its own, LIMIT takes the first N rows in whatever order the query happens to produce. PostgreSQL is blunt about the risk: when using LIMIT, it is important to use an ORDER BY clause The clause that sorts a query's result before LIMIT takes its first N rows; it has its own lesson. Full entry → that constrains the result rows into a unique order; otherwise you will get an unpredictable subset of the query's rows, because SQL does not promise to deliver results in any particular order. That is why the classic pairing sorts first and caps second. The ten most recent orders fall out of one short query: SELECT * FROM orders ORDER BY placed_at DESC LIMIT 10; — ORDER BY puts the newest order first, and LIMIT keeps the first ten. ORDER BY has its own lesson; here it is enough to know the pairing exists.
LIMIT for pagination: showing results in pages
Applications rarely show a thousand results at once; they show pages. Pagination is simply the habit of showing results in pages, and LIMIT builds it with a partner clause called OFFSET. PostgreSQL documents OFFSET in one line: OFFSET says to skip that many rows before beginning to return rows. SQLite states the combined effect precisely: if a SELECT statement has an OFFSET clause, then the first M rows are omitted from the result set and the next N rows are returned. Page one of a ten-row-per-page product list is SELECT * FROM products LIMIT 10;, and page two skips the first ten: SELECT * FROM products LIMIT 10 OFFSET 10;. Same cap, different starting point.
Database differences: LIMIT here, TOP there
Not every database spells the clause the same way, and the honest note is a short one: MySQL, PostgreSQL, and SQLite use LIMIT; SQL Server uses TOP; Oracle uses FETCH FIRST n ROWS ONLY, per W3Schools. Microsoft Learn documents TOP as limiting the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server, written before the column list: SELECT TOP 5 * FROM orders;. The operation is identical — cap the result at a number of rows — and only the keyword differs. This is a difference to know, not a war to pick: every example in this lesson uses LIMIT, the common form across MySQL, PostgreSQL, and SQLite.
LIMIT does not change the data
LIMIT is part of a SELECT, and SELECT is a read. SQLite puts the boundary in one sentence: SELECT never changes the database. A capped query is still a read: SELECT * FROM products LIMIT 5; looks at the products table and brings back five rows, and running it a hundred times changes nothing in the table. The rows beyond the cap are not deleted, hidden, or moved — they simply are not in this particular answer. That makes LIMIT safe to experiment with, and it is the same guarantee that SELECT itself carries, covered in its own lesson.
The honest framing: small but essential
LIMIT is one keyword, and it looks trivial next to joins and subqueries. The honest framing is that it is small but essential. W3Schools gives the performance reason: the SELECT TOP clause is useful on large tables with thousands of records, because returning a large number of records can impact performance. Every list screen, search result, and dashboard uses LIMIT to keep queries fast and screens readable, and every report that shows the top ten leans on the pairing with ORDER BY. It is the clause you will type in almost every real query, precisely because it is the one that keeps the answer down to what a person can actually look at.
Eli explains
The same idea, in plain words
Explain it like I’m 10
LIMIT is the clause that caps a query's answer. Write a number at the end of a SELECT and the database returns only the first N rows of the result: SELECT * FROM products LIMIT 5; brings back the first five products and nothing else. The rest of the table is untouched — LIMIT only trims what comes back, and it never deletes or changes anything. On its own it takes the first rows in whatever order the query happens to produce, so the classic move is to sort first with ORDER BY and then cap, like the ten most recent orders. And with a partner clause called OFFSET, LIMIT pages through results, showing ten at a time.
Picture it like this
Think of the high-score board at an arcade. The machine has played a hundred games, but the board shows only the top ten scores — sorted from highest to lowest, then cut off at ten. That is LIMIT with ORDER BY in one glance: sort the scores, keep the first ten, and let the rest stay in the machine's memory. The arcade board is also a page: it shows scores one through ten, and if the cabinet had a page two, it would show eleven through twenty — the same board, ten at a time.
Where the picture stops working
The board comparison stops short in one way: the arcade machine decides which scores are best, while LIMIT never decides anything — it takes the first rows of whatever order the query produced, and without ORDER BY that order can be unpredictable. And the board hides the lower scores permanently, while a limited query hides nothing: the table still holds every row, and the next query can bring back any of them.
Worked example
Harbor Market's orders table holds a year of orders, one row each, with a placed_at date column. The owner wants the ten most recent orders for a report, so she runs SELECT * FROM orders ORDER BY placed_at DESC LIMIT 10;. ORDER BY placed_at DESC sorts the newest order first, and LIMIT 10 keeps only the first ten rows of that sorted result. The report shows ten orders. When the store adds a second screen for the next ten, she runs the same query with an offset: SELECT * FROM orders ORDER BY placed_at DESC LIMIT 10 OFFSET 10;, which skips the ten already shown and returns the next ten. After both queries, the orders table holds exactly what it held before — every row, in place.
Key takeaway
LIMIT caps a query at the first N rows — the small, essential clause that pairs with ORDER BY, powers pagination, and reads without changing a single row.
Quick check
3 questions here, of 5 in this lesson’s practice set. Answers stay hidden until you check.
Harbor Market's products table has 140 rows. Which query returns only the first five products?
Choose an answer, then check it.
A manager wants the ten most recent orders from the orders table, which has a placed_at date column. Which query does the job?
Choose an answer, then check it.
Study tools & related lessonsYou’ll learn to · Common mistakes · Easily confused · Key vocabulary · Related
You’ll learn to
- Define LIMIT as the clause that returns only the first N rows of a query's result, using the working definitions of SQLite and W3Schools.
- Write the basic shape of a limited query: LIMIT N at the end of a SELECT statement, as in SELECT * FROM products LIMIT 5;.
- Explain why LIMIT is paired with ORDER BY to get the first N of a sorted list, using the ten most recent orders as the example.
- Describe pagination as showing results in pages, with LIMIT and OFFSET moving from one page to the next.
- Recognize that MySQL, PostgreSQL, and SQLite use LIMIT while SQL Server uses TOP, and that LIMIT never changes the data it reads.
Common mistakes
Expecting LIMIT to return the best or most recent rows without ORDER BY.
LIMIT takes the first N rows of whatever order the query produces. To get the newest, the cheapest, or the highest, sort first: PostgreSQL warns that without an ORDER BY constraining the rows into a unique order, you get an unpredictable subset.
Thinking LIMIT deletes or hides the other rows.
LIMIT only trims the answer a query returns. The rows beyond the cap stay in the table untouched — SQLite states that SELECT never changes the database.
Writing LIMIT in SQL Server.
SQL Server uses TOP: SELECT TOP 5 * FROM orders;, not LIMIT 5. MySQL, PostgreSQL, and SQLite are the dialects that spell the clause LIMIT.
Expecting LIMIT 5 to return exactly five rows every time.
Five is a cap, not a quota. PostgreSQL notes that no more than that many rows will be returned, but possibly fewer if the query itself yields fewer rows — a three-row table answers LIMIT 5 with three rows.
Easily confused
LIMIT (MySQL, PostgreSQL, SQLite) vs. TOP (SQL Server)
The same operation — cap the result at a number of rows — spelled differently by vendor; Microsoft Learn documents TOP for SQL Server while W3Schools shows LIMIT for MySQL.
LIMIT alone vs. LIMIT with ORDER BY
Alone, LIMIT takes the first N rows of an unpromised order; with ORDER BY it takes the first N of a sorted list, the classic pairing behind top-ten lists.
LIMIT vs. OFFSET
LIMIT caps how many rows come back; OFFSET skips rows before the result begins. Together they page through a table, ten at a time.
A limited result vs. The stored table
The query returns a slice of the table, computed at that moment; the table itself keeps every row, because LIMIT never changes data.
Key vocabulary
- LIMIT clause
- The part of a SELECT query, written at the end, that caps the result at N rows and returns only the first N rows of the result set.
- result set
- The rows and columns a SELECT query returns, shaped like a small table; LIMIT trims it to the first N rows.
- first N rows
- The N rows that come first in a query's result, which is exactly what LIMIT N returns.
- OFFSET
- The clause paired with LIMIT that skips a set number of rows before the capped result begins.
- pagination
- Showing the results of a query in pages, using LIMIT to size each page and OFFSET to move to the next one.
- TOP clause
- SQL Server's name for the same operation as LIMIT: it caps the rows a query returns at a specified number.
- ORDER BY clause
- The clause that sorts a query's result before LIMIT takes its first N rows; it has its own lesson.
Sources & references
- SQL SELECT TOP, LIMIT and FETCH FIRST Clause — W3Schools
- PostgreSQL Documentation: 7.6. LIMIT and OFFSET — PostgreSQL Global Development Group
- SQLite Query Language: SELECT — SQLite Consortium
- TOP (Transact-SQL) — SQL Server — Microsoft Learn
EliExplains lessons are original prose written from the open, credible references above. See Copyright & Licensing.
Researched 2026-08-21
Educational content only. It is not medical, legal or professional advice. Found an error? Tell us.