You now have every piece of a query but one: deciding how many rows you want to receive. With the 20 rows of products it looks irrelevant, but the day you connect to a production table with fifty million records, a bare SELECT * FROM orders; can bring your terminal down, saturate the network and get a systems administrator looking for you with a face like thunder. LIMIT is the first line of defence. And it's also the basis of pagination, one of those problems that look solved in two minutes and that on large tables become a classic headache. In this lesson you'll learn both: the everyday use and the serious one.

1. LIMIT: capping the result
2. Why LIMIT without ORDER BY isn't deterministic
3. OFFSET and classic pagination
4. The two problems of OFFSET on large tables
5. Cursor or keyset pagination
6. FETCH FIRST n ROWS ONLY: the standard form
7. Typical use cases
8. LIMIT in the logical execution order
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

LIMIT n states how many rows, at most, you want to receive. It's written at the end of the query, after ORDER BY:

The five most expensive products in the catalogue. This pattern —sort and trim— is the canonical way of answering any "top N" question.

LIMIT's behaviour in the edge cases:

The most frequent day-to-day use doesn't even carry an ORDER BY: it's taking a look at a table you don't know.

Here SELECT * and the absence of ORDER BY are perfectly legitimate: you don't want particular rows, you want to see what the data looks like. It's exploration, not a report.

Professional habit: when you connect to a database you don't know, write LIMIT 10 before writing the rest of the query. It costs eight characters and saves you the embarrassment of locking up a production session.

We pick up the warning from 02-05, because with LIMIT it becomes far more dangerous.

What exactly did you ask for? Any three rows. Not the first three by id, not the three cheapest: three arbitrary rows, whichever the engine had closest to hand. That today they turn out to be 1, 2 and 3 is a coincidence of the physical order.

The difference from a query without LIMIT is subtle but crucial:

In the first case, sorting the result in your application fixes the problem. In the second there's no possible fix: you've lost 17 rows and you don't know which.

Rule: LIMIT without ORDER BY is only acceptable for exploring. As soon as the result is used for anything, the ORDER BY is mandatory, and it has to be deterministic (ending in a unique column, as you saw in 02-05).

OFFSET m discards the first m rows of the result before applying LIMIT. Combined, they let you walk a large result in chunks:

Let's see it with GreenStore's catalogue sorted by price descending and pages of five products.

Page 1 — LIMIT 5 OFFSET 0:

Page 2 — LIMIT 5 OFFSET 5:

Page 3 — LIMIT 5 OFFSET 10:

And the fourth and last would close with products 2, 18, 14, 4 and 5.

A summary of the arithmetic:

Important details:

• The ORDER BY is mandatory. Without it, each page is computed over a different order and you could see the same product on two pages and another on none.
• The ORDER BY must be deterministic. That's why we've written ORDER BY price DESC, id: if two products cost the same, the tie-break by id guarantees they always fall on the same page.
• OFFSET without LIMIT is valid: OFFSET 15 returns the rows from the 16th onwards.
• OFFSET larger than the total returns zero rows, with no error. OFFSET 100 over products gives nothing.

This pagination works, it's the one everybody learns, and it has two serious flaws that only show up when the data grows.

OFFSET m doesn't skip the first m rows: it computes them and throws them away. The engine has no way of guessing which row is number 500,001 without having produced the previous 500,000.

The last page of a large listing can take hundreds of times longer than the first. It's a known pattern: users complain that "the site is slow at the end of the catalogue", and the cause is right here. With EXPLAIN ANALYZE (module 8) you can see it perfectly clearly in the Limit node, which reports how many rows it discarded.

This one is worse, because it isn't slow: it's wrong.

Imagine a user is looking at page 1 of the catalogue sorted by price descending and, at that very moment, somebody adds a €25 product (more expensive than the matcha). When the user hits "next":

The user sees product 13 twice. And with a deletion the opposite happens: a row disappears without ever having been shown.

Each page is an independent query, run at a different moment and over a different state of the table. OFFSET has no memory of what it already showed you.

When can you live with OFFSET?

The alternative consists of no longer saying "skip 500,000 rows" and starting to say "give me what comes after this". Instead of a position, you remember the last value seen.

Let's go back to our catalogue. Page 1 ended with product 13, at €13.75. Page 2 is requested like this:

Identical to page 2 from section 3, but obtained without discarding anything: the engine goes straight to the rows that satisfy price < 13.75.

What happens if there are ties. If two products cost exactly €13.75, WHERE price < 13.75 would skip the second. The solution is to compare the complete tuple of the sort columns, something SQL allows:

The comparison (price, id) < (13.75, 13) is lexicographic: it holds if price < 13.75, or if price = 13.75 and additionally id < 13. It's exactly the condition "strictly after the last item seen" for that order. Careful with one detail: for the tuple comparison to line up, every ORDER BY column has to run in the same direction, which is why id DESC appears here.

A comparison of the two approaches:

In practice: if your interface is an infinite scroll or a "load more" button, keyset is always the right answer. If you need clickable page numbers, OFFSET is the only viable option and you have to accept its limits (or cap the number of navigable pages, which is what nearly every search engine does).

For keyset to perform you need an index over the sort columns, in this case (price, id). Without it, PostgreSQL has to sort the whole table anyway and you gain nothing. Indexes are module 8.

LIMIT is convenient, universally known... and it isn't standard SQL. MySQL introduced it, PostgreSQL adopted it and today nearly every engine understands it, but the official SQL:2008 syntax is another one:

Exactly the same result. PostgreSQL accepts both forms and treats them identically.

PostgreSQL also adds WITH TIES, which widens the result to include every row tied with the last one:

On this data it returns the same 5 rows, because no two products share a price. But if there were three products at €13.75, WITH TIES would return 7 rows instead of 5: it's the correct way to do an honest "top 5" in a competition. WITH TIES requires ORDER BY and doesn't work with LIMIT, only with FETCH.

A comparison table across engines:

Two traps in that table worth underlining:

1. MySQL's LIMIT m, n reverses the meaning: the first number is the offset and the second the count. LIMIT 5, 10 in MySQL is LIMIT 10 OFFSET 5 in PostgreSQL. A query copied without reading it returns the wrong data.
2. Oracle's ROWNUM is assigned before the ORDER BY. WHERE ROWNUM <= 5 ORDER BY price DESC returns five arbitrary rows and then sorts them: it isn't the top 5. You have to sort in a subquery and apply ROWNUM outside.

Which one should you use? In this course, LIMIT: it's what you'll see in 99 % of PostgreSQL code. If you write SQL that has to run on several engines, OFFSET ... FETCH FIRST ... ROWS ONLY is the safe bet.

You've already seen it with the most expensive products. Another variant, the five with the highest margin:

Notice that the Spirulina capsules (margin €7.70, third in the catalogue) don't appear: WHERE active discards them before sorting. The logical order rules: first you filter, then you sort, and only at the end do you trim.

The last three orders received: the natural content of a "recent activity" panel.

GreenStore's first customer. This pattern —ORDER BY ... LIMIT 1— is equivalent to using MIN/MAX, but with an advantage: it gives you the whole row, not just the minimum value. With MIN(signup_date) you'd know the date, but not who it was. Aggregate functions arrive in lesson 04-04.

With LIMIT the cycle we started in 02-01 is complete. This is the module's definitive diagram:

LIMIT is always last. Consequences worth being clear about follow from that:

That last point deserves a nuance. Even though logically everything is sorted, PostgreSQL is smarter than that: when it sees an ORDER BY ... LIMIT n with a small n, it uses an algorithm called a top-N heapsort, which keeps only the best n rows seen so far in memory instead of sorting the entire set. And if an index exists that already returns the rows in the requested order, it can read the first n and stop, without touching the rest of the table.

It's the perfect example of the distinction from 02-01: the logical order defines the meaning of the query, and the physical plan the optimizer picks can be radically more efficient as long as it produces the same result. You'll see it with your own eyes in module 8 with EXPLAIN.

• LIMIT without ORDER BY in any query that isn't exploratory. It returns arbitrary rows and the problem can't be fixed afterwards.
• A non-deterministic ORDER BY when paginating. If there are ties and you don't break them with a unique column, the same row can appear on two pages and another on none.
• Copying LIMIT 10, 20 from a MySQL example. In MySQL it means "20 rows starting at the 11th"; in PostgreSQL it isn't even valid syntax.
• Believing OFFSET skips rows for free. It produces them and throws them away. On page 5,000 you'll notice.
• Paginating with OFFSET over data that changes. Repeated rows and skipped rows, with no error to give it away.
• Applying keyset with an ORDER BY that doesn't match the WHERE condition. The columns must be the same, in the same order and in the same direction.
• Using LIMIT to "fix" a slow query. If the query is slow for lack of an index, LIMIT can still be slow: the engine has to find the rows before trimming them.
• Expecting LIMIT 1 to replace a well-written WHERE. Returning any old row among those that meet the condition is rarely what you want.
• Tip: LIMIT first, query afterwards. When exploring an unfamiliar database, write it before anything else.
• Tip: LIMIT 0 is useful. It gives you the columns and their types without fetching data, ideal for checking the shape of a complex query.
• Tip: for an honest "top N", consider FETCH FIRST n ROWS WITH TIES. Cutting at exactly number 5 when 5 and 6 are tied is misleading.

Customer support needs a panel with the five oldest orders that are neither delivered nor cancelled, showing id, customer_id, order_date, status and shipping_cost. Write the query and explain how many rows it actually returns and why.

Build the second page of a customer listing sorted by country and, within each country, by surname, with pages of four customers. Write it first with OFFSET and then with keyset pagination. Explain what information the application needs in each case to request the next page.

The purchasing team wants the three order lines with the highest amount. Write the query using the standard FETCH FIRST syntax, and state what would change if you used WITH TIES. Then answer: why can't this query tell you which product is involved?

Solution 1

5 rows, which turn out to be all the ones meeting the condition: of the 20 orders, 14 are delivered and 1 cancelled, so only 5 remain in progress. The LIMIT 5 hasn't trimmed anything, and that's normal behaviour: LIMIT is a maximum, not a promise.

The reasoning by the logical order: FROM brings 20 rows → WHERE leaves 5 → SELECT projects → ORDER BY order_date, id sorts them from oldest to most recent → LIMIT 5 discards none. If ten new orders come in tomorrow, the query will still return the five oldest without changing a comma.

In module 4 you'll write that condition more cleanly as WHERE status NOT IN ('delivered', 'cancelled').

Solution 2

With OFFSET, page 2 (customers 5 to 8 of the listing):

The complete listing sorted by country, last_name starts like this: Dubois (9), Moreau (10), Almeida Nunes (8), Moreira Costa (7) —page 1—, and continues:

With keyset pagination, starting from the last item of page 1 —Sofia Moreira Costa, country Portugal, surname Moreira Costa, id 7—:

It returns exactly the same four rows.

What the application needs in each case:

Notice two details of the keyset: the WHERE's tuple contains exactly the same columns as the ORDER BY and in the same order, and id is included as the last column precisely to break ties between two customers who shared country and surname. Without that id, a repeated surname would cause a customer to be lost between one page and the next.

Solution 3

What would change with WITH TIES. Nothing, in this case: the fourth line is worth €26.73, a long way from the third's €34.02, so there's no tie to drag in. But it's worth noting that WITH TIES can't carry an artificial tie-break in the ORDER BY: if you leave the trailing , id, every row has a unique sort value and there will never be ties to include. For WITH TIES to be of any use you have to sort only by the real criterion:

On this data it returns the same 3 rows. If there were two lines tied at €34.02, it would return 4.

Why you can't tell which product is involved. Because order_lines stores product_id, a number, and the product's name lives in the products table. With this module's tools you can only query one table at a time: the most you can say is that line 28 corresponds to product 15. To write "Ceremonial matcha green tea 30 g" in the report you need to combine both tables, and that's exactly what starts in the next lesson.

You've closed the full cycle of a query:

• LIMIT n caps the result at a maximum of n rows and is the first line of defence when exploring large or unfamiliar tables.
• LIMIT without ORDER BY isn't deterministic: it returns arbitrary rows, and that damage can't be repaired afterwards.
• OFFSET m discards the first m rows and enables the classic LIMIT size OFFSET (N-1)*size pagination, which demands a deterministic ORDER BY.
• That pagination has two flaws on large tables: the cost grows with the page number, and insertions or deletions cause rows to repeat or be skipped.
• Cursor or keyset pagination —WHERE (columns) < (last values seen)— has constant cost and never drifts out of sync, at the price of not being able to jump to an arbitrary page.
• FETCH FIRST n ROWS ONLY is the standard form, and WITH TIES widens the result with the tied rows. Each engine has its dialect, and MySQL's LIMIT m, n reverses the arguments.
• LIMIT is step 5 and the last of the logical order; the optimizer, however, does take advantage of its presence with strategies such as the top-N heapsort.

And with this you close the whole of module 2. You know how to build a query from start to finish: choose its source with FROM, filter rows with WHERE, project and compute columns with SELECT and its aliases, remove repetitions with DISTINCT, impose an order with ORDER BY and trim the result with LIMIT. And you know in what logical order all of that happens, which is what has let you understand why an alias works in one place and not in another.

But notice the ceiling you've hit again and again: orders tells you customer_id = 9 and not "Camille Dubois"; order_lines tells you product_id = 15 and not "Ceremonial matcha green tea"; products tells you category_id = 2 and not "Natural cosmetics". Every one of your queries has looked at a single table, and the questions GreenStore really cares about —what each customer bought, which category bills the most, which sales rep closes the most orders, which products nobody has bought— live precisely in the relationships between tables that you drew in the diagram in 01-06. In module 3, Queries with Multiple Tables, you'll learn to walk those relationships with JOIN: the INNER JOIN for what matches on both sides, the LEFT and RIGHT for keeping what doesn't match —there the three customers with no orders and the three never-sold products will finally show up—, the FULL OUTER for both at once, the SELF JOIN for the reflexive relationships of employees and customers, and UNION, INTERSECT and EXCEPT for combining whole results. GreenStore's nine tables stop being nine islands.