Offset-Fetch in MS SQL Server

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 :

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 number Name Course
111 Riya CSE
112 Apoorva ECE
113 Mina Mech
114 Rita Biotechnology
115 Veena Chemical
116 Deepa EEE

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 number Name Course
113 Mina Mech
114 Rita Biotechnology
115 Veena Chemical
116 Deepa EEE

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 number Name Course
117 Vani Mech
118 Megha ECE

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.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up


If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.