SQL OFFSET-FETCH

SQL OFFSET-FETCH 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 pagination support.

Example

#

Problem: 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

SQL OFFSET

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.

SQL OFFSET FETCH

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List 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