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.