SQL OFFSET-FETCH

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

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

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

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.

SELECT Id, ProductName, UnitPrice, Package
  FROM Product
 ORDER BY UnitPrice DESC
OFFSET 10 ROWS

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.

SELECT Id, ProductName, UnitPrice, Package
  FROM Product
 ORDER BY UnitPrice DESC
OFFSET 10 ROWS
 FETCH NEXT 5 ROWS ONLY

Tip: This is how applications generally implement pagination.

• Our Sql SELECT Reference
• Our Sql SELECT TOP Reference
• Our Sql SELECT DISTINCT Reference
• Our Sql ORDER BY Reference