0

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

enter image description here

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

enter image description here

while restored DB shows first row

enter image description here

I thought it might be due to different collation, but I checked both servers have same collation.

3
  • 2
    Try adding with ties to the top clause. – vonPryz Apr 20 at 9:36
  • 5
    For deterministic results, the specified ORDER BY columns must be unique. Try ORDER BY verification_code, created_date DESC, person_id. – Dan Guzman Apr 20 at 10:01
  • hanks for reply, my original question was 'why different behavior despite same data & same query' – user1543848 Apr 20 at 11:02
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.

3
  • Original DB is on SQL Server 2014, while same DB is restored on SQL Server 2017. This might be the issue that same query on same data fetch different TOP 1 row on different versions of SQL Server. – user1543848 Apr 20 at 17:03
  • 1
    @user1543848, Yes, the difference between one engine and the other could be one more thing influencing on the different result, but is it clear for you now that none of the results are wrong? In fact you could have four different result for that query considering that sample data and they should not surprise you. It's the expected behavior. Even if you would run that same query repeatedly only on your SQL Server 2014, you could not expect to get the same result all the time for a nondeterministic request like that. – Ronaldo Apr 20 at 17:18
  • 3
    Here's a simple way to see it: imagine I give you ten U$100 bills and ask you to order them by value and give me the one at the top. All the bills are of U$100 so you put them on the order you prefer and give me the one at the top. You did what I asked, yet, if later I shuffle those same ten bills and ask you to order them by value and give me the top one again, chances are 1/10 that you're gonna give me the same bill against 9/10 that it's gonna be another one. How do I know that it's not the same bill? They all have a unique serial number, but I didn't mention it on my request to you. – Ronaldo Apr 20 at 17:34
1

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

4
  • updated result set, please – user1543848 Apr 20 at 9:38
  • 3
    Verification_Code is null, therefore ordering is "nondeterministic". Now it would depend on PK, clustered index etc, but nondeterministic. – Vladislav Zalesak Apr 20 at 10:47
  • Thanks for reply, my original question was 'why different behavior despite same data & same query' – user1543848 Apr 20 at 11:01
  • 1
    The answer is in the comments, @user1543848. Same non-determenistic query. Different results are expected. – ypercubeᵀᴹ Apr 20 at 11:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.