Offset in SQL

Let us consider a situation where we need to display only a specific number of rows within a given range. In this case, we might have to remove a specific set of rows from the beginning and display them till the upper bound of the range specified as per requirement. In such scenarios, we use the OFFSET command in SQL.

What is Offset in SQL?

Offset in SQL is used to eliminate a set of records from a given table in order to retrieve a set of records according to the requirement of the database.

Basically, it is used to find a starting point to display a set of rows as a final output. By using OFFSET, we discard the desired number of rows from the beginning of the table till the number mentioned with the OFFSET command.

How to Use Offset in SQL?

OFFSET in SQL is generally used with the ORDER BY clause with a value greater than or equal to zero.

Syntax using OFFSET:

In conjunction with the OFFSET command, we use the FETCH to retrieve the required set of rows. FETCH mostly returns the number of rows and completely depends on the OFFSET command.

Syntax using OFFSET and FETCH:

Example of OFFSET in SQL Server

Let's look at a few examples to learn how to use OFFSET and FETCH:

Employees table:

1. Display the contents of the table, removing the first row

Query:

Output:

2. Display the table excluding the first five rows.

Output:

3. Display the 6th and 7th row after using offset

Output: