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 ROWSONLY

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

SELECT CityID,CityName
FROM (
    SELECTCityID,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

Sql Query to fetch in between records from a table


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

Leave a Reply

Your email address will not be published. Required fields are marked *