Sql Query to fetch in between records from a table

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.

SELECT  *

FROM CityMaster

ORDER BY CityID ASC

OFFSET  30 ROWS

FETCH NEXT 20 ROWS ONLY

Or else you can also use the below query - Without OFFSET FETCH clause

SELECT CityID,CityName

FROM (

    SELECT CityID,CityName , ROW_NUMBER() OVER (ORDER BY cityid) AS RowNum

    FROM CityMaster

) AS MyDerivedTable

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