CODEWITHSUNDEEP
sqlsql-server
Telavian
Telavian

Reputation: 3832

Defined SQL behavior when ordering by values with same value

I am paging through a very large result set with page size of 5000 and ordering by a last updated date time. I am also selecting all records with a last updated time less than the start of the operation.

Everything is working correctly however I will occasionally see records more than a single time. I was wondering what the defined behavior is when a ordered column shares the same value.

For instance if I order by column X and three rows have a value of 5 for that column. Will those values always be in the same order using the same order by expression?

Upvotes: 1

Views: 211

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

There is no guarantee that the same order will always be applied.

If there are no column used in ORDER BY, then the result will be random. You might observe repeatable results (meaning the same order for several runs), but you cannot depend on it to be always true. You may want to add another column in the ORDER BY to get consistent results.

Upvotes: 2

B. Murtin
B. Murtin

Reputation: 26

It's the same concept as a primary key.. You need to be able to uniquely identify the data so the program has no choice but to return the data the same way.. Consider adding another column to your ORDER BY statement and use ASC or DESC to make sure it prints out the same way.

Upvotes: 0

Related Questions