Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest self-service freelancing marketplace for people like you.

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



Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest self-service freelancing marketplace for people like you.

Guides


vsn 3.1