CODEWITHSUNDEEP
sql-server-2012
Davide Martelli
Davide Martelli

Reputation: 319

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.

Upvotes: 4

Views: 940

Answers (3)

Slappy
Slappy

Reputation: 5472

I had the same issue: the query always returned the same rows independently on the Offset value (30):

OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY

For me this comment by veljasije is the correct answer:

Weird, maybe your column in ORDER BY is not deterministic, and make problem. Can field column can contain duplicates? If so, add primary key column in ORDER BY clause, after field column.

I solved this issue by adding [ID] column into Order By:

ORDER BY [Status], [ID] ASC

Upvotes: 5

Sparhawk_
Sparhawk_

Reputation: 172

OFFSET FETCH NEXT freaks out if the column that you are ordering by does not contain unique data. To “fake” a distinct column you can do this:

SELECT * FROM table 
ORDER BY ROW_NUMBER() over (order by field) 
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

Upvotes: 2

veljasije
veljasije

Reputation: 7092

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;

Upvotes: 2

Related Questions