Sql Query to fetch in between records from a table

By
Advertisement
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

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

0 comments:

Post a Comment

Online Casino