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.

This article is contributed by Anuj Chauhan. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

My Personal Notes arrow_drop_up

Save