CODEWITHSUNDEEP
sqlsql-server-2012
JIemON
JIemON

Reputation: 397

Strange behavior OFFSET ... ROWS FETCH NEXT ... ROWS ONLY after ORDER BY

I see very strange behavior of construction like 

SELECT ... ORDER BY ... OFFSET ... ROWS FETCH NEXT ... ROWS ONLY

I made simple example to display it (SQL Server version: 2012 - 11.0.5058.0 (X64) ): 

CREATE TABLE #temp( c1 int NOT NULL, c2 varchar(MAX) NULL) 
INSERT INTO #temp (c1,c2) VALUES (1,'test1')
INSERT INTO #temp (c1,c2) VALUES (2,NULL)
INSERT INTO #temp (c1,c2) VALUES (3,'test3')
INSERT INTO #temp (c1,c2) VALUES (4,NULL)
INSERT INTO #temp (c1,c2) VALUES (5,NULL)

First query:

select * from #temp 
ORDER BY c2 DESC

Result is ok:

3   test3
1   test1
2   NULL
4   NULL
5   NULL

Second query: 

select * from #temp 
ORDER BY c2 DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY

Result has another sorting:

3   test3
1   test1
4   NULL

And last query:

select * from #temp 
ORDER BY c2 DESC
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY

Result is very strange and invalid in my opinion (I need get two records that not contains in previous result, but instead I get record with id=4 second time):

4   NULL
2   NULL

Can anyone explain why SQL server works so strange?

Upvotes: 0

Views: 1015

Answers (1)

user5757984
user5757984

Reputation: 11

Use unique key column with this type of case:

select * from #temp 
ORDER BY c2, c1 DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY

Never faced ordering problem.

Upvotes: 1

Related Questions