Offset in SQL - Scaler Topics

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.

Transform Your Career

Choose from our industry-leading programs designed for career success

NSDC Certified

Modern Software and AI Engineering Program

Master full-stack development with AI integration

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Modern Data Science and ML with specialisation in AI

Advanced data science techniques with AI specialization

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Advanced AIML with Specialisation in Agentic AI

Deep dive into AIML with focus on Agentic systems

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

DevOps, Cloud & AI Platform Engineering

Build and manage AI-powered cloud infrastructure

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

AI Engineering Advanced Certification by IIT-Roorkee

Premier AI engineering certification from IIT-Roorkee

3 MonthsDuration

AI-LedCurriculum

Career SupportSupport

Go to Program

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:

Emp_ID	Emp_name	Emp_gender	Emp_age
101	Amar Rathore	Male	27
102	Dimple Khanna	Female	28
103	Mayuri Chatterji	Female	27
104	Karthik Padman	Male	30
105	Aisha Khan	Female	29
106	Dianna	Female	27
107	Jaspreet	Male	30

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

Query:

Output:

Emp_name	Emp_gender
Mayuri Chatterji	Female
Dianna	Female
Dimple Khanna	Female
Aisha Khan	Female
Karthik Padman	Male
Jaspreet	Male

2. Display the table excluding the first five rows.

Output:

Emp_name	Emp_gender
Karthik Padman	Male
Jaspreet	Male

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

Output:

Emp_name	Emp_gender
Karthik Padman	Male
Jaspreet	Male

Scaler Placement Report and Statistics

₹23L

AVG CTC

SCALER PLACEMENT PROOF

Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.

11,000+placements

650+companies

Verified data

See full placement report

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.