SQL | OFFSET-FETCH Clause
Last Updated :
27 Dec, 2021
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,
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:
- OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
- TOP cannot be combined with OFFSET and FETCH.
- The OFFSET/FETCH row count expression can be only be any arithmetic, constant, or parameter expression which will return an integer value.
- ORDER BY is mandatory to be used with  OFFSET and FETCH clause.
- OFFSET value must be greater than or equal to zero. It cannot be negative, else return error.
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...