SQL | OFFSET-FETCH Clause

OFFSET and FETCH Clause are used in conjunction with SELECT and ORDER BY clause to provide a means to retrieve a range of records.

OFFSET

The OFFSET argument is used to identify the starting point to return rows from a result set. Basically, it exclude the first set of records.
Note:

• OFFSET can only be used with ORDER BY clause. It cannot be used on its own.
• OFFSET value must be greater than or equal to zero. It cannot be negative, else return error.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
ORDER BY column_name
OFFSET rows_to_skip ROWS;

Examples:
Consider the following Employee table,

• Print Fname, Lname of all the Employee except the employee having lowest salary.

  SELECT Fname, Lname
  FROM Employee
  ORDER BY Salary
  OFFSET 1 ROWS;

  Output:

  

FETCH

The FETCH argument is used to return a set of number of rows. FETCH can’t be used itself, it is used in conjunction with OFFSET.
Syntax:

SELECT column_name(s)
FROM table_name
ORDER BY column_name
OFFSET rows_to_skip
FETCH NEXT number_of_rows ROWS ONLY;

Example:

• Print the Fname, Lname from 3rd to 6th tuple of Employee table when sorted according to the Salary.

  SELECT Fname, Lname
  FROM Employee
  ORDER BY Salary
  OFFSET 2 ROWS
  FETCH NEXT 4 ROWS ONLY;

  Output:

  

• Print the bottom 2 tuples of Employee table when sorted by Salary.

  SELECT Fname, Lname
  FROM Employee
  ORDER BY Salary
  OFFSET (SELECT COUNT(*) FROM EMPLOYEE) - 2 ROWS
  FETCH NEXT 2 ROWS;

  Output:

  

Important Points:

1. OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
2. TOP cannot be combined with OFFSET and FETCH.
3. The OFFSET/FETCH row count expression can be only be any arithmetic, constant, or parameter expression which will return an integer value.
4. ORDER BY is mandatory to be used with  OFFSET and FETCH clause.
5. OFFSET value must be greater than or equal to zero. It cannot be negative, else return error.