CODEWITHSUNDEEP
sql-serversql-server-2012
Hung Doan
Hung Doan

Reputation: 1167

Get unexpected result from: ORDER BY [ColumnName] ASC OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY

Description: unexpected paging result with ORDER BY [Column A ] ASC OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY.If [Column A ] have the same value.

NOTE: I use SQL Server 2012 Express

Condition:

  1. Use expression: ORDER BY [Column A] ...

  2. [Column A] : all rows have the same value.

Preproduction:

Case 1: 

SELECT [Id],[Date]    
FROM [TestDateoffset]  
ORDER BY [Date] ASC
OFFSET 0 ROWS

Current result: 

Id   Date 
---------------------------------------
1    2015-01-24 21:40:30.0441628 +07:00 
2    2015-01-24 21:40:30.0441628 +07:00 
3    2015-01-24 21:40:30.0441628 +07:00 
4    2015-01-24 21:40:30.0441628 +07:00

Expected result: 

Id  Date 
-----------------------------------
1   2015-01-24 21:40:30.0441628 +07:00 
2   2015-01-24 21:40:30.0441628 +07:00 
3   2015-01-24 21:40:30.0441628 +07:00 
4   2015-01-24 21:40:30.0441628 +07:00

Status : PASS

Case 2: 

SELECT [Id],[Date]    
FROM [TestDateoffset]  
ORDER BY [Date] ASC
OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY

Actual result:

Id   Date
---------------------------------------
2    2015-01-24 21:40:30.0441628 +07:00

Expected result:

Id  Date
---------------------------------------
1   2015-01-24 21:40:30.0441628 +07:00

Status : FAIL

Case 3: 

SELECT [Id],[Date]    
FROM [TestDateoffset]  
ORDER BY [Date] ASC
OFFSET 1 ROWS FETCH FIRST 1 ROWS ONLY

Actual result:

Id   Date
---------------------------------------
2    2015-01-24 21:40:30.0441628 +07:00

Expected result:

It have to different to: 

Id   Date
---------------------------------------
2    2015-01-24 21:40:30.0441628 +07:00

Status : FAIL

The result always be the same if I run OFFSET 1 ROWS, or OFFSET 2 ROWS ,..:

Id   Date
---------------------------------------
2    2015-01-24 21:40:30.0441628 +07:00

My question:

  1. Is it a bug ?

  2. In case 2, case 3: how does it work? Why I always got 2nd record?

I think the "order" process behind "ORDER BY COLUMN ASC" and "ORDER BY COLUMN ASC OFFSET 0 ROWS FETCH FIRST 1" are different. But I don't know why and what is the rule !

Upvotes: 1

Views: 216

Answers (1)

Andomar
Andomar

Reputation: 238206

You're ordering on Date, but all dates are equal, so SQL Server can return the rows in any order.

To order on id when dates are equal, you could use:

ORDER BY Date, Id OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY
              ^^^^

Upvotes: 3

Related Questions