Click here to Skip to main content
14,699,489 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I'm having a table of Employees. It has EmployeeName (varchar(100)) and Salary field (deciaml). All the records are in sorted order, and I was trying to fetch records from 11th Employee to 20 Employee. I know there are multiple and simple ways to get this data and I've also done it with 3-4 different ways. But, one of the way that I tried is not working and it retruns unexpected data. Below are the scripts that I'm using:

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

Select * from Employee


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


Explanation: First, I'm getting records of first 20 employees, using TOP(20). Then, I'm trying to order the data of 20 Employee in descending order and then get TOP(10) employees. It supposed ot return me Employee from 11-20 (in any order descinduing or ascinding). But, it returns employees from 50-41. Why?

What I have tried:

I've tried on google, all are talking about Views and TOP but in my case there is no View.
Posted
Updated 3-Sep-16 10:00am

Probably, because you don't specify an ORDER BY condition on the idder set: so SQL is at liberty to return rows in any order it finds suitable. You then take the top 20 of those, order them by ID, and select the top 10 of that result.
If you want to extract specific rows with TOP, you always need to specify an ORDER BY or you have no real control over what rows are returned.
   
select top(10) e.*
from (Select Top(20) e.*
      from Employee e
      order by e.id 
     ) e
order by e.id desc;


This is the final query that returns expected result. Thanks to @OriginalGriff for pointing out the problem in query.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900