SQL OFFSET-FETCH Clause

  • OFFSET excludes the first set of records.
  • OFFSET can only be used with an ORDER BY clause.
  • OFFSET with FETCH NEXT returns a defined window of records.
  • OFFSET with FETCH NEXT is great for building pagination support.
The definitive guide
for data professionals


Order today!
 See 2 min video



The SQL ORDER BY OFFSET syntax


The general syntax to exclude first n records is:
SELECT column-names
  FROM table-name
 ORDER BY column-names
OFFSET n ROWS

To exclude first n records and return only the next m records:
SELECT column-names
  FROM table-name
 ORDER BY column-names
OFFSET n ROWS
 FETCH NEXT m ROWS ONLY

This will return only record (n + 1) to (n + 1 + m). See example below.



SQL OFFSET-FETCH Examples




Problem: Get all but the 10 most expensive products sorted by price
SELECT Id, ProductName, UnitPrice, Package
  FROM Product
 ORDER BY UnitPrice DESC
OFFSET 10 ROWS


Results: 68 records.

Id ProductName UnitPrice Package
27 Schoggi Schokolade 43.90 100 - 100 g pieces
63 Vegie-spread 43.90 15 - 625 g jars
8 Northwoods Cranberry Sauce 40.00 12 - 12 oz jars
17 Alice Mutton 39.00 20 - 1 kg tins
12 Queso Manchego La Pastora 38.00 10 - 500 g pkgs.




Problem: Get the 10th to 15th most expensive products sorted by price
SELECT Id, ProductName, UnitPrice, Package
FROM Product
ORDER BY UnitPrice DESC
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY


Results: 5 records

Id ProductName UnitPrice Package
27 Schoggi Schokolade 43.90 100 - 100 g pieces
63 Vegie-spread 43.90 15 - 625 g jars
8 Northwoods Cranberry Sauce 40.00 12 - 12 oz jars
17 Alice Mutton 39.00 20 - 1 kg tins
12 Queso Manchego La Pastora 38.00 10 - 500 g pkgs.