Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Offset-Fetch in MS SQL Server

  • Last Updated : 29 Jul, 2020

Suppose a table has 30 rows. A user wants to extract list of last 10 rows and skip topmost rows. To make job easier, the offset-fetch clause is used in the query.

Syntax :

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

order by column_list[ASC|DESC]
Offset offset_row count{ROW|ROWS}
Fetch{FIRST|NEXT} fetch row_count {ROW|ROWS} only


Analysis of Syntax :



  • Order by clause is mandatory to use alongside offset-fetch clause otherwise it results in an error. ASC arranges rows from ascending to descending order while DESC arranges rows from descending to ascending order.
  • Offset skips number of rows specified in a table.
  • Fetch returns number of rows after offset clause is being used. It returns specified number of rows.
  • FIRST returns number of rows at start of table after offset is while NEXT returns consecutive rows after first set of rows.

Example –


Table – Student
Roll numberNameCourse
111RiyaCSE
112ApoorvaECE
113MinaMech
114RitaBiotechnology
115VeenaChemical
116DeepaEEE

If the user wants to skip first two rows and return rest of them, query is given as –

select name, rollnumber, course
from student 
order by rollnumber ASC
offset 2 ROWS

Output –

Roll numberNameCourse
113MinaMech
114RitaBiotechnology
115VeenaChemical
116DeepaEEE

Offset skips number of rows specified in query while order by ASC arranges rows from ascending to descending order. If a user wants to skip first 6 rows and fetch the next ones, query is given as –

select name, rollnumber, course 
from student
order by roll number ASC
offset 6 ROWS
fetch FIRST 2 ROWS ONLY

Output –

Roll numberNameCourse
117VaniMech
118MeghaECE

Offset clause skips all rows specified in table while Fetch clause returns first two rows after offset clause. In the fetch clause, FIRST and NEXT can be used according to user’s requirements. Offset clause is mandatory to use while fetch is optional for use in queries.

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!