CODEWITHSUNDEEP
sqlsql-server-2008selectsql-order-by
Romesh
Romesh

Reputation: 2274

Outer Query OrderBy does not depends on Inner Query's Resultset

I am not having deep knowledge of SQL execution order.

When I Execute a Query

select top 2 * from Configuration

It Gives me 

ABC1,100,Data001    
ABC2,200,Data002

When I Execute a Query

select top 2 * from Configuration order by 1 desc

It Gives me

XYZ1,400,Data100    
XYZ2,300,Data099

When I Execute a Query

select * from (select top 2 * from Configuration) as a order by 1 desc

It gives me 

XYZ1,400,Data100    
XYZ2,300,Data099

My problem is why i am getting

XYZ1,400,Data100    
XYZ2,300,Data099

as output instead

ABC1,100,Data001    
ABC2,200,Data002

As per my knowledge Inner Query Will Return two rows to Outer Query. Outer Query Will process those two rows with 

From->Where->group by->having->Select->Order by

order of Execution and Will give output as two rows which I mentioned as expected. But Outer Query's order by is affecting on whole table of Inner Query.

Please comment where I am making mistake.

Upvotes: 3

Views: 409

Answers (1)

Anvesh
Anvesh

Reputation: 7693

SQL Server has no any sense to populate by default order by. In your inner query you have not specified order by so it returned wrong result. Now use below code for SQL Server 2008 R2

SELECT * FROM (SELECT TOP 2 * FROM Configuration ORDER BY 1) AS a ORDER BY 1 DESC

One other alternative is : Comman Table Expression , Like below, 

;WITH cteTest AS
(
    SELECT TOP 2 * 
    FROM Configuration ORDER BY 1
)
SELECT * FROM cteTest ORDER BY 1 DESC

Upvotes: 4

Related Questions