CODEWITHSUNDEEP
sqlt-sqlsql-order-by
einord
einord

Reputation: 2324

using offset rows in T-SQL change sort order

I recently discovered that when using "offset x rows" with the "fetch next x rows only" and "order by" command, items with the same value may appear in a different order.

Example:

SELECT * FROM Rows ORDER BY [IntegerField] OFFSET 0 ROWS

Output:
ID  IntegerField
1   1
2   1
3   2

But by adding fetch next 50 rows only

SELECT * FROM Rows ORDER BY [IntegerField] OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

Output:
ID  IntegerField
2   1
1   1
3   2

In real life I'm using a GUID data type as an identifier, so maybe that is the reason for this weird behavior?

Upvotes: 1

Views: 534

Answers (1)

xxxo
xxxo

Reputation: 136

I had the same problem but found this on MSDN :

The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.

ORDER BY Clause

It is not specified that because the result contains twice (or more) the same value the order is not guaranteed but I guess this is implicit.

Upvotes: 1

Related Questions