Assume an Orders table with 2 columns (OrderID, customerID) and we try to write the following query:
USE NORTHWIND GO SELECT * FROM (SELECT orderid, customerid FROM dbo.Orders ORDER BY orderid) AS O
If you try to execute the above query, it will not work. I asked you the reason and the solution to make this work.
The reason why this does not work is becuase ORDER BY clause returns a cursor rather than a relational result set. The outer query expects a table (relational result set) but the table expression in the inner query is returning a cursor causing an error.
If you want this query to work, include a TOP clause in the inner query as follows:
USE NORTHWIND GO SELECT * FROM (SELECT TOP 100 PERCENT orderid, customerid FROM dbo.Orders ORDER BY orderid) AS D;
Mind you, TOP is not an ANSi standard. Its T-SQL specific. TOP clause will convert the cursor back to a result set. Its interesting to see how nonstandard tricks can be applied to get the code working.
Also note that ORDER BY is an expensive clause. Use it only if you required.