Search code examples
sql-server-2012

sql server 2012 offset return same record

I have a strange problem with SQL Server 2012.

I use a query like this

SELECT * FROM table ORDER BY field OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

Every time I use this query from 0 to 25, 25 to 50, 50 to 75, 75 to 100, it returns the same 25 records.

When I use it with 100 to 125, it returns the original 25 to 50 etc...

My table contains only 197 records and this happens only with one table of my database. All other tables work correctly.

This also happens when I use the query via code (ASP.NET C#) and from SQL Management Studio directly.

Solution

  • You must change your logic a little bit, to include which page is active:

    DECLARE @pagesize AS BIGINT = 25, @pagenum AS BIGINT = 3; 

SELECT * 
FROM table 
ORDER BY field 
OFFSET @pagesize * @pagenum ROWS FETCH NEXT @pagesize ROWS ONLY;