In this article i am going to expalin the query used to fetch the inbetween records from a table
For example if my requirement is to fetch from 30th to 50th record from citymaster table then i can use the below query. We can achieve it using OFFSET FETCH clause.
The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.
Below is the example query used to fetch from 30th to 50th record from citymaster table.
Or else you can also use the below query – Without OFFSET FETCH clause
SELECTCityID,CityName ,ROW_NUMBER()OVER (ORDER BY cityid) AS RowNum
WHERE MyDerivedTable.RowNum BETWEEN 31 AND 50
The sample output will be
Limitations in Using OFFSET-FETCH:
1.ORDER BY is mandatory to use OFFSET and FETCH clause.
2.OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
3.TOP cannot be combined with OFFSET and FETCH in the same query expression.
4.The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.
If you have any doubt in this article then please add it in comments