By the end of this lesson you can write top-N queries and page queries with LIMIT and OFFSET, explain why they need a deterministic ORDER BY, and decide when ties at the cut-off should be included.
Why it matters: "Top 10 products", "the 20 most recent orders" and page 3 of a customer list in an app are all LIMIT queries. Getting the ORDER BY wrong produces top-10 lists that aren't the top 10, and pages that repeat or skip rows.
Dataset: Wadi Market online store. Examples run in the editor on the right.
LIMIT n keeps at most n rows of the result. It is applied last, after WHERE has filtered and ORDER BY has sorted, so ORDER BY … LIMIT n means the first n rows of the sorted result: a top-N query.
Without ORDER BY, LIMIT still works, but it returns any n rows: whichever the database happens to produce first. That's fine for a quick peek at a table (SELECT * FROM orders LIMIT 5), never for a report.
SELECT …
FROM …
WHERE …
ORDER BY …
LIMIT row_count OFFSET rows_to_skip;SELECT name, category, price
FROM products
ORDER BY price DESC
LIMIT 3;| name | category | price |
|---|---|---|
| Dallah | Kitchen | 210 |
| Dates box | Grocery | 145 |
| Lamp | Home | 129 |
| Coffee | Grocery | 89 |
| Finjan set | Kitchen | 75.5 |
| Incense | Home | 49 |
ORDER BY sorts the whole result first; LIMIT then keeps the first 3 rows. Without ORDER BY, “the first rows” have no guaranteed order.
SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC, product_name
LIMIT 5;PostgreSQL sorts all 61 products by price (highest first, name as tie-breaker) and keeps the first 5, led by the two Falcon Book laptops.
Ties at the cut-off
What if the 5th and 6th products have the same price? LIMIT 5 cuts between them, and which one you get depends on the tie-breaker (or on luck, if there is none). Sometimes that's acceptable; sometimes the honest answer to the top 3 includes everyone tied for third place. The SQL-standard syntax FETCH FIRST n ROWS WITH TIES does exactly that: it keeps the first n rows plus every further row equal to the n-th on the ORDER BY keys. So with WITH TIES you deliberately leave out the unique tie-breaker, or nothing would ever tie.
SELECT product_name, category, price
FROM products
ORDER BY category
FETCH FIRST 1 ROW WITH TIES;Sorted by category, the first row is an Electronics product. WITH TIES then adds every other row with the same category, so both Electronics products come back although we asked for 1 row. With LIMIT 1 you'd get only one of them.
Paging with OFFSET
OFFSET k skips the first k rows of the sorted result. Apps use it to show results page by page: with a page size of 20, page 1 is LIMIT 20 OFFSET 0, page 2 is LIMIT 20 OFFSET 20, and page p is LIMIT 20 OFFSET (p - 1) * 20. OFFSET counts rows, not pages: OFFSET 2 skips two rows.
Three pitfalls:
- The order must be deterministic. If rows tie on the sort keys, the database may order tied rows differently each time a page is requested, so a row can appear on two pages, or on none. End the ORDER BY with a unique column.
- Data changes between requests. If a new row is inserted before page 2 is fetched, every later row shifts by one, and the user sees a duplicate.
- Large offsets are slow.
OFFSET 100000still makes PostgreSQL produce and throw away 100,000 rows. Production apps often use keyset pagination instead: remember the last row shown and ask for rows after it, e.g.WHERE (signup_date, customer_id) > ('2024-05-02', 4711) ORDER BY signup_date, customer_id LIMIT 20.
SELECT customer_id, first_name, last_name, signup_date
FROM customers
ORDER BY signup_date, customer_id
LIMIT 20 OFFSET 20;Customers are sorted by signup date with customer_id as a unique tie-breaker (several customers signed up on the same day). OFFSET 20 skips page 1, and LIMIT 20 returns rows 21 to 40.