SQL OFFSET-FETCH Clause

How do I implement pagination in SQL?

OFFSET excludes the first set of records.
This keyword can only be used with an ORDER BY clause.

OFFSET with FETCH NEXT returns a defined window of records.
OFFSET with FETCH NEXT is wonderful for building pagination support.

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 the 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 + m). See example below.

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued

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
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.

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.


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