SQL OFFSET-FETCH Clause

Key Points

OFFSET is used for pagination.

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.


Example

List all but 10 of the largest orders, sorted by amount.

SELECT *
  FROM [Order]
 ORDER BY TotalAmount DESC
OFFSET 10 ROWS

Syntax

ORDER BY OFFSET syntax -- excludes first n records.

SELECT column-names
  FROM table-name
 ORDER BY column-names
OFFSET n ROWS

ORDER BY OFFSET syntax -- excludes first n records and return only next m records.

SELECT column-names
  FROM table-name
 ORDER BY column-names
OFFSET n ROWS
 FETCH NEXT m ROWS ONLY

Note: This returns only records (n + 1) to (n + m). See example below.


More Examples

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List all but the 10 most expensive products, sorted by price.
SELECT Id, ProductName, UnitPrice, Package
  FROM Product
 ORDER BY UnitPrice DESC
OFFSET 10 ROWS
Result:  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.
Another example
PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
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
Result:  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.

You may also like



Guides


Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas.      Vsn 1.2.2