Open In App

SQL | OFFSET-FETCH Clause

Improve
Improve
Like Article
Like
Save
Share
Report


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.

Last Updated : 27 Dec, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads