SQL OFFSET-FETCH Clause

The SQL ORDER BY OFFSET syntax

The general syntax to exclude first n records is:

  1. SELECT columnnames
  2. FROM tablename
  3. ORDER BY columnnames
  4. OFFSET n ROWS

To exclude first n records and return only the next m records:

  1. SELECT columnnames
  2. FROM tablename
  3. ORDER BY columnnames
  4. OFFSET n ROWS
  5. FETCH NEXT m ROWS ONLY

This will return only record (n + 1) to (n + 1 + m). See example below.


 

SQL OFFSET-FETCH Examples

Problem: Get all but the 10 most expensive products sorted by price

  1. SELECT Id, ProductName, UnitPrice, Package
  2. FROM Product
  3. ORDER BY UnitPrice DESC
  4. OFFSET 10 ROWS

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

 


Problem: Get the 10th to 15th most expensive products sorted by price

  1. SELECT Id, ProductName, UnitPrice, Package
  2. FROM Product
  3. ORDER BY UnitPrice DESC
  4. OFFSET 10 ROWS
  5. FETCH NEXT 5 ROWS ONLY

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

1 thought on “SQL OFFSET-FETCH Clause

  1. I just want to mention I’m very new to blogs and definitely savored you’re web blog. Very likely I’m going to bookmark your blog post . You definitely come with outstanding writings. Regards for revealing your website page.

댓글 남기기

이메일은 공개되지 않습니다.