Strange behavior of TOP 1 clause sql server 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.
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