All we need is an easy explanation of the problem, so here it is.
I have a SQL Server database and restored it on another server on a different machine. Now I run following same query on both DBs which produces same below result set.
select person_id, visit_id, verification_code, mobileNo, created_date from person order by verification_code, created_date DESC
But when I add TOP 1 clause to the above query, original DB shows the 2nd row (ignores first row)
select TOP 1 person_id, visit_id, verification_code, mobileNo, created_date from person order by verification_code, created_date DESC
while restored DB shows first row
I thought it might be due to different collation, but I checked both servers have same collation.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Mike D. has provided an answer on how SQL Server sorts data that might help you understand what is it about the deterministic result you see on some comments.
The Arguments of the
ORDER BY doc says:
ASC | DESC
Specifies that the values in the specified column should be
sorted in ascending or descending order. ASC sorts from the lowest
value to highest value. DESC sorts from highest value to lowest value.
ASC is the default sort order. Null values are treated as the lowest
NULL values went to the top of your result for being the lowest possible values and since the
create_date for those rows are all the same, according to the set theory, it doesn’t make any difference which one of the four
NULL values are presented first.
But the data in your order by clause doesn’t provide any ordering
verification_code is null
and all values for
created_date are the same
you have to give the engine a little bit to work with
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂