Top with OrderBy in Sql Server

Suppose there are 50 employees in our Employee table. And our job is to fetch employees from 11th to 20th position. There are so many ways of doing this, you can use OFFSET that is introduced in Sql Server 2012, you can use EXCEPT etc. Below are the queries for EXCEPT and OFFSET:

Data script:
declare @counter int = 1
while(@counter <= 50)
begin
insert into Employee (name,salary) select CONCAT('name - ',@counter),100*@counter;
set @counter += 1;
end
GO
Select * from Employee

Query with OFFSET:
SELECT *
FROM Employee e
ORDER BY e.id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY

Query with EXCEPT:
Select Top(20) * From Employee
Except
Select Top(10) * From Employee

We are supposed to get the same result (as per above queries) using TOP, see below query:

Query with TOP:
select top(10) * from (
Select Top(20)* from Employee
) as t order by t.id desc

But above query producess unexpected result. It supposed to produce employees from 11th to 20th position. But instead of that it returns employees from 50th to 41th position, why?

Answer: When we use TOP, it should never be used without ORDER BY. If we use it without ORDERBY then it returns some unexpected result, that’s why we are getting list of employees from 50th to 41th position. So, to fix our query we’ll have to add ORDER BY in our inner query too. See below working script:

select top(10) e.*
from (Select Top(20) e.*
from Employee e
order by t.id asc
) e
order by e.id desc;

So, Thumb rule is, NEVER USE TOP WITHOUT ORDER BY.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s