ORDER BY in both queries of the EXCEPT clause

Question

I am loking to implement a paging query in ANSI SQL.

I tried the following query in SQL Server, but it did not allow it:

select top 20 * from MyTable order by id  
except
select top 10 * from MyTable order by id

But the following works fine:

select top 20 * from MyTable 
except
select top 10 * from MyTable order by id

e.g.:

input   expected result
20      11
11      13
25      14
6       16
4       17
2       18
1       19
9       20
3       25
7       
8       
16      
17      
18      
13      
14      
19      
10      
5   

As seen from the above example, if we cannot write the first ORDER BY clause then the result will not be having the values 20, 11, 25.

Answer 1

I agree, this should work, order by in conjunction with top still produces a table (as oposed to just order by which produces a cursor and thus cannot be used as a select source).

Of course, you can work around this limitation easily:

select * from (select top 20 * from MyTable order by id) x
except
select * from (select  top 10 * from MyTable order by id) y

Answer 2

the order by clause needs to appear only once, after the last-occurring query being compared by the except operator. order by is the last clause to be evaluated in the phases of logical query processing .