Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 125 ยท Querying Data

LIMIT, OFFSET and Pagination

Return only the rows you need with SQL LIMIT and OFFSET. Build top-N queries, page through results, use fast keyset pagination and learn TOP and FETCH FIRST.

Tables grow. A shop with a few dozen products today may have a hundred thousand next year, and nobody wants to scroll through all of them at once. Most screens show a slice of the data: the five newest products, the top ten customers, page 3 of the search results. In SQL you get that slice with LIMIT (how many rows) and OFFSET (how many rows to skip first).

This lesson shows how to write top-N queries, how classic page-number pagination works, why it gets slow on large tables, and the faster keyset technique that big sites use. You'll also see how SQL Server, Oracle and the SQL standard spell the same idea.

LIMIT: keep the first n rows

LIMIT n goes at the very end of a query and keeps at most n rows. It is almost always paired with ORDER BY, because "the first five rows" only means something once you've said what first means:

SQL The newest products ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The query sorts all 26 products from newest to oldest and returns the first five, starting with Watercolor Set (added on 2024-12-10). If a table has fewer rows than the limit, you simply get all of them; LIMIT is a maximum, not an exact count.

LIMIT without ORDER BY returns arbitrary rows. SELECT * FROM products LIMIT 5 gives you some five products, typically the first ones the database happens to read. That's fine for a quick peek at a table, but never for anything a user sees: the rows can change when the data or the query plan changes.

The database processes LIMIT last: it filters with WHERE, sorts with ORDER BY and only then cuts the result. So LIMIT combined with WHERE gives you "the first n rows that match":

SQL
SELECT name, price
FROM products
WHERE stock = 0
ORDER BY price DESC
LIMIT 2;   -- the two most expensive out-of-stock products

OFFSET: skip rows first

OFFSET m skips the first m rows of the sorted result before LIMIT counts. Together they describe a window into the result: "skip 5, then take 5" is rows 6 to 10. That is exactly what page 2 of a five-per-page list needs:

SQL Page through customers ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Page 1 shows the two Adeyemis and the first three Anderssons. Page 2 continues with the fourth Andersson, then two Bauers and two Brandts. The id at the end of ORDER BY makes the order unique, so no customer can land on two pages. Change the offset to 10 to see page 3.

For page number p and page size s, the formula is:

TEXT
OFFSET = (p - 1) * s

A web application usually passes p from the URL (?page=3) and computes the offset in code, then sends both numbers to the database as query parameters.

How many pages are there?

To draw page links you need the total number of rows. Count them with the same WHERE (but without ORDER BY, LIMIT or OFFSET), and round up when dividing:

SQL Counting pages ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

There are 40 customers, which is 8 pages of 5 or 6 pages of 7. Adding size - 1 before an integer division is a classic trick for rounding up: SQLite divides two integers into an integer, dropping the remainder (more on that in Calculated Columns).

The comma form

SQLite and MySQL also accept LIMIT offset, count with a comma. Note the reversed order: the offset comes first.

SQL
LIMIT 10, 5   -- skip 10, take 5: the same as LIMIT 5 OFFSET 10

It's easy to misread, so prefer the explicit LIMIT ... OFFSET ..., which also works in PostgreSQL.

Top-N queries and ties

"Top 3" questions are just ORDER BY ... DESC LIMIT 3. But ties can make the answer misleading. In the company database, ask for the ten best-paid employees:

SQL Top 10 with a tie ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first query returns ten rows, and the tenth is one of three people who each earn 93,000. Which one you get is arbitrary. The second query first finds the 10th-highest salary with LIMIT 1 OFFSET 9 inside a subquery, then returns everyone who earns at least that much: 12 rows, with all three tied employees. (Subqueries are explained in Subqueries.)

Some databases have this built in as WITH TIES:

SQL
-- PostgreSQL 13+, Oracle 12c+, SQL standard
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS WITH TIES;

-- SQL Server
SELECT TOP (10) WITH TIES first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

For "top N per group" (the three best-paid people in each department), LIMIT alone isn't enough. You'll solve that with window functions in ROW_NUMBER, RANK and DENSE_RANK.

The nth highest value

A classic interview question: find the second-highest salary. Combine DISTINCT (so ties don't count twice) with OFFSET:

SQL Second-highest salary ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The second-highest salary is 157,000. The second query asks for the 11th-highest distinct salary, which is 89,000. Compare that with the previous example, where the 11th row was still 93,000: without DISTINCT, the three tied salaries take up three positions. DISTINCT explains it in detail.

Why OFFSET gets slow

OFFSET looks cheap, but the database can't jump straight to row 100,000. It has to produce the first 100,000 rows of the sorted result, throw them away and then return the next 20. Page 1 is fast; page 5,000 reads 100,000 rows to show 20. On large tables, deep pages get slower and slower.

There's a correctness problem too. If a new row is inserted while a user is reading page 1, every later row shifts down by one, and the user sees the last row of page 1 again at the top of page 2. Deletions make a row skip past unseen.

Keyset pagination

Keyset pagination (also called seek or cursor pagination) avoids both problems. Instead of "skip 40 rows", the query says "give me the rows after the last one I showed". The application remembers the sort values of the last row on the current page and passes them to the next query.

Here, products are listed from cheapest to most expensive, with id as a tie-breaker so the order is unique. Page 1 ends with Bamboo Cutting Board (price 18.5, id 2), so page 2 starts after that pair:

SQL Keyset pagination ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The second and third queries return the same page, starting with Foam Roller at 19.9. (price, id) > (18.5, 2) is a row value comparison: it compares price first and uses id only when the prices are equal, exactly like the sort order. SQLite (3.15+), PostgreSQL and MySQL support row values; SQL Server doesn't, so use the expanded OR form there.

With an index on (price, id), the database jumps straight to the right place in the index, so page 5,000 is as fast as page 1, and inserted or deleted rows never cause duplicates or gaps.

LIMIT / OFFSET Keyset
Jump to page 37 directly Yes No, only next/previous
Speed on deep pages Gets slower Constant (with an index)
Stable when rows are added No Yes
Good for Admin tables, small data, numbered pages Feeds, infinite scroll, APIs, exports

Keyset needs a unique sort. The last sort column must be unique (usually the primary key). Otherwise rows with the same values as the last row of a page can be skipped.

Top percent: the best 10%, not the best 10 rows

Sometimes the cut-off is a share of the table rather than a fixed count: "the most expensive 10% of products", "the top quarter of customers". SQL Server and Oracle have a keyword for it. SQLite, PostgreSQL and MySQL don't, but the idea is easy to rebuild.

SQL
-- SQL Server: TOP ... PERCENT (rounds the row count UP)
SELECT TOP (10) PERCENT name, price
FROM products
ORDER BY price DESC;

SELECT TOP (10) PERCENT WITH TIES name, price   -- keep rows tied with the last one
FROM products
ORDER BY price DESC;

-- Oracle 12c+ and the SQL standard: FETCH FIRST ... PERCENT
SELECT name, price
FROM products
ORDER BY price DESC
FETCH FIRST 10 PERCENT ROWS ONLY;

SELECT name, price
FROM products
ORDER BY price DESC
FETCH FIRST 10 PERCENT ROWS WITH TIES;

Both spellings turn the percentage into a row count with ceil(total_rows ร— percent / 100). With 26 products, 10% is 2.6, so you get 3 rows: the database never returns a fraction of a row, and it never rounds down to zero while a table has any rows at all. TOP ... PERCENT and FETCH FIRST ... PERCENT are not available in PostgreSQL (its FETCH FIRST takes a row count only) or in MySQL, so there you compute the count yourself.

The portable recipe is to count the rows, convert the percentage to a whole number with integer arithmetic (adding 99 before dividing by 100 rounds up) and hand that to LIMIT. SQLite lets LIMIT take a scalar subquery:

SQL Top 10 percent with LIMIT ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

There are 26 products, so the subquery returns (260 + 99) / 100 = 3, and you get the three priciest: Ultralight Tent, Bookshelf Speakers and Adjustable Dumbbells. PostgreSQL accepts the same subquery in LIMIT; MySQL doesn't allow a subquery there, so it needs a prepared statement or a window function (next example).

A window function version works in every modern database, including MySQL 8 and SQL Server, and it can also reproduce WITH TIES: RANK() gives tied rows the same rank, so a rule like "rank within the first N rows" keeps every row that ties with row N.

SQL Top 25 percent, ties included ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

A quarter of 26 rows rounds up to 7, and the query returns seven products from Ultralight Tent down to Monitor Arm. No two of them share a price, so RANK() and ROW_NUMBER() agree here; with tied prices at the boundary, RANK() would keep all the tied rows, exactly like WITH TIES.

Two near-misses are worth knowing:

  • NTILE(4) splits the rows into four buckets of nearly equal size (7, 7, 6 and 6 rows for 26 products) and numbers them 1 to 4. It is the right tool for "which quartile is each row in?", but bucket 1 is only equal to "the top 25%" when the row count divides evenly.
  • PERCENT_RANK() is (rank โˆ’ 1) / (rows โˆ’ 1), so PERCENT_RANK() <= 0.25 is a different cut-off from "the first 25% of the rows". It describes where a row sits; it does not count rows. The percentiles lesson covers it in detail.

Decide what ties and rounding should do. "Top 10%" is ambiguous for small tables and for tied values. Round up (as TOP ... PERCENT does) when you must never return fewer rows than the share implies, and use RANK()/WITH TIES when two rows with the same price must stay together or go together.

LIMIT in other databases

LIMIT is not part of the SQL standard. The standard spelling is OFFSET ... FETCH, and SQL Server has its own TOP:

Database First 10 rows Rows 21โ€“30
SQLite, PostgreSQL, MySQL, MariaDB LIMIT 10 LIMIT 10 OFFSET 20
SQL standard, PostgreSQL, Oracle 12c+ FETCH FIRST 10 ROWS ONLY OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
SQL Server SELECT TOP (10) ... OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY (requires ORDER BY)
Oracle 11g and older WHERE ROWNUM <= 10 nested queries with ROWNUM
SQL
-- SQL Server
SELECT TOP (5) name, price
FROM products
ORDER BY price DESC;

-- PostgreSQL, Oracle 12c+, SQL Server 2012+
SELECT name, price
FROM products
ORDER BY price DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

SQLite has two small extras: LIMIT -1 means "no limit" (useful when you need an OFFSET without a limit), and LIMIT and OFFSET accept expressions, such as LIMIT 2 * 5.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which rows does ORDER BY id LIMIT 10 OFFSET 20 return from a table with ids 1 to 100?

    OFFSET 20 skips ids 1 to 20, and LIMIT 10 keeps the next ten: 21 to 30.
  2. 2. In SQLite, what does LIMIT 30, 10 mean?

    In the comma form the offset comes first: LIMIT 30, 10 equals LIMIT 10 OFFSET 30.
  3. 3. What's the main problem with SELECT * FROM products LIMIT 5;?

    Rows have no inherent order. Add ORDER BY to say which five rows you want.
  4. 4. Salaries are 100, 90, 90, 80. What does SELECT DISTINCT salary FROM t ORDER BY salary DESC LIMIT 1 OFFSET 1 return?

    DISTINCT reduces the values to 100, 90 and 80. Skipping one leaves 90 as the second-highest salary.
  5. 5. Why is keyset pagination faster than OFFSET on deep pages?

    OFFSET 100000 has to produce and throw away 100,000 rows. A condition such as (price, id) > (?, ?) lets the database seek directly in an index.

Summary

  • LIMIT n keeps at most n rows; always combine it with ORDER BY so you know which rows you get.
  • OFFSET m skips m rows first; page p of size s uses LIMIT s OFFSET (p - 1) * s.
  • SQLite and MySQL also accept LIMIT offset, count (offset first); prefer the explicit form.
  • Top-N results can cut through ties; use FETCH FIRST ... WITH TIES / TOP ... WITH TIES where available, or a subquery.
  • Deep OFFSETs are slow and unstable when data changes; keyset pagination (WHERE (sort_col, id) > (last values)) is fast and stable.
  • For "top n percent" use TOP (n) PERCENT (SQL Server) or FETCH FIRST n PERCENT ROWS ONLY (Oracle, standard); elsewhere compute ceil(rows ร— n / 100) and pass it to LIMIT, or filter on RANK().
  • The standard spelling is OFFSET ... ROWS FETCH NEXT ... ROWS ONLY; SQL Server also has TOP.

Next: DISTINCT, where you'll remove duplicate rows from your results.