SQL OFFSET-FETCH

OFFSET excludes the first set of rows in a result set.

OFFSET can only be used with an ORDER BY clause.

OFFSET with FETCH NEXT returns a defined window of rows.

OFFSET with FETCH NEXT is great for pagination support.

Example

#

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

SELECT *
  FROM [Order]
 ORDER BY TotalAmount DESC
OFFSET 10 ROWS
Result:  820 records
Id OrderDate OrderNumber CustomerId TotalAmount
268 2013-04-23 00:00:00.000 542645 63 10588.50
232 2013-03-19 00:00:00.000 542609 65 10495.60
293 2013-05-19 00:00:00.000 542670 63 10191.70
444 2013-10-03 00:00:00.000 542821 63 10164.80
785 2014-04-17 00:00:00.000 543162 89 8902.50

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

OFFSET n ROWS

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List all but the 10 of the 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.

OFFSET n ROWS, FETCH NEXT

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.

Tip: This is how applications generally implement pagination.


You may also like



Guides


vsn 3.1