Home Dashboard Directory Help
Search

TOP N Sort and Sort operator returning different order when ORDER BY column has same value by Debdutta


Status: 

Closed
 as By Design Help for as By Design


1
3
Sign in
to vote
Type: Bug
ID: 778807
Opened: 2/7/2013 11:46:26 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

The TOP 1 EmpId is not returning the same record what I can see the First record without using TOP 1. As a example, the table #Employee has three columns, EmpId, EmpName and DOJ (Date of Joining). I wanted to pick up the First Record in descending order of Date of Joining. I Observed, if DOJ is same for first few records then TOP 1 is not returning the first record what it's returning without TOP 1 sort operator with same ORDER BY.
Now, I write two queries. One with TOP all and another with without TOP with same ORDER BY to return all the records and check the output of record order. I can see the record order of the output result is different. When I see the execution plan, I can see to sort there are two different operator one is Top N Sort and another is Sort. Does this two different soft operator work differently? Finally I wrote the query with ROW_NULBER function and getting the desire output. Is it a bug that Top N Sort operator change the order what it returns by the order by? OR please let me know what algorithm follow by the Top N sort and sort when there is a tie in Order by column value.
Details
Sign in to post a comment.
Posted by Microsoft on 7/26/2013 at 2:07 PM
Hello Debdutta. Thanks for sharing your feedback with us. As Martin has explained, the behavior in question is by design.

-Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Posted by Microsoft on 7/26/2013 at 2:06 PM
Hello Debdutta. Thanks for sharing your feedback with us. As Martin has explained, the behavior in question is by design.

-Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Posted by Martin Smith on 2/9/2013 at 5:23 AM
This is not a bug. If you want deterministic results you need to add the column you want to use as the tie breaker into the ORDER BY clause.
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
TOP_N_Sort_vs_Sort.sql 2/7/2013 993 bytes