SQLDailyDrills

LIMIT and OFFSET

Return only some of the rows: the first n, or a page starting after m rows.

Syntax

SELECT … ORDER BY … LIMIT n [OFFSET m];
SELECT … ORDER BY … OFFSET m ROWS FETCH FIRST n ROWS ONLY;
  • The FETCH form is standard SQL. SQL Server uses TOP or OFFSET … FETCH; Oracle uses FETCH FIRST.

Example

SELECT name, unit_price FROM products ORDER BY unit_price DESC, name LIMIT 3 OFFSET 3;

The second page of three products, most expensive first.

Tables used: products. Try it in a lesson below.

Common mistakes

  • Without ORDER BY, which rows you get is not defined.
  • A large OFFSET still reads and skips all earlier rows, so deep pages get slow.

Practise it