What is Offset in SQL?

quiz
Challenge Inside! : Find out where you stand! Try quiz, solve problems & win rewards!

What is 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.

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:

SELECT column_names
  FROM table_name
 ORDER BY column_names
OFFSET n ROWS //n is the number of rows to be excluded.

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:

SELECT column_names
  FROM table_name
 ORDER BY column_names
OFFSET n ROWS   //exclude first n rows
 FETCH NEXT n ROWS ONLY //returns next n rows

Example of OFFSET in SQL Server

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

Employees table:

Emp_IDEmp_nameEmp_genderEmp_age
101Amar RathoreMale27
102Dimple KhannaFemale28
103Mayuri ChatterjiFemale27
104Karthik PadmanMale30
105Aisha KhanFemale29
106DiannaFemale27
107JaspreetMale30

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

Query:

SELECT Emp_name, Emp_gender
FROM employees
ORDER BY age
OFFSET 1 ROWS;

Output:

Emp_nameEmp_gender
Mayuri ChatterjiFemale
DiannaFemale
Dimple KhannaFemale
Aisha KhanFemale
Karthik PadmanMale
JaspreetMale

2. Display the table excluding the first five rows.

SELECT Emp_name, Emp_gender
FROM employees
ORDER BY age
OFFSET 5 ROWS;

Output:

Emp_nameEmp_gender
Karthik PadmanMale
JaspreetMale

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

SELECT Emp_name, Emp_gender
FROM employees
ORDER BY age
OFFSET 5 ROWS
 FETCH NEXT 2 ROWS ONLY

Output:

Emp_nameEmp_gender
Karthik PadmanMale
JaspreetMale

Learn more

To learn more about SQL, Click here.

Conclusion

  • We learned that OFFSET is generally used to discard rows from the beginning of the table.
  • It is used along with the ORDER BY clause.
  • To return a specific number of rows, the FETCHcommand is used.
  • FETCH is used along with OFFSET and holds no meaning otherwise.
Free Courses by top Scaler instructors