Strange behavior of TOP 1 clause sql server

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

Strange behavior of TOP 1 clause sql server

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

Strange behavior of TOP 1 clause sql server

while restored DB shows first row

Strange behavior of TOP 1 clause sql server

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.

Method 1

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
possible values.

All the 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.

Method 2

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply