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.

Emarginate

Wow... select * is bad enough, but oh so much worse when we have no schema to work with. – Jabot

@ebyrob I have used select * just to illustrate , it could be any thing other than * – Emarginate

@thunder nah, you do have enough info for a good answer here. I'm just in a mood. – Jabot

@stakx I am looking for something that works for all databases . – Emarginate

Actually, you’re off to a bad start with TOP which is not ANSI standard. – Afterworld

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

Cyme

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 .

Widen

I added an example of why the order by is required in both queries – Emarginate

use select...into to insert the results of the select top...order by queries into a couple of temp tables. then perform the except. – Widen

temp tables seems to be viable option , but not so elegant isn't it ?or is it just my thinking. – Emarginate

@Emarginate If you're worried about elegance, read the article I linked. Notice the "elegant" triple-query is horribly inefficient. – Jabot

Answer 3

This article dug out of the comment above is great:

http://www.codeproject.com/Articles/6936/Paging-of-Large-Resultsets-in-ASP-NET

In particular the Asc-Desc method:

This method uses default ordering in a subquery and then applies the reverse ordering. The principle goes like this

DECLARE @temp TABLE (
    PK  /* PK Type */ NOT NULL PRIMARY 
)

INSERT INTO @temp 
SELECT TOP @PageSize PK FROM (
    SELECT TOP (@StartRow + @PageSize) 
    PK, 
    SortColumn /*If sorting column is defferent from the PK, SortColumn must 
             be fetched as well, otherwise just the PK is necessary */ 
    ORDER BY SortColumn /* default order – typically ASC */) 
ORDER BY SortColumn /* reversed default order – typically DESC */

SELECT ... FROM Table JOIN @Temp temp ON Table.PK = temp.PK 
ORDER BY SortColumn /* default order */

Of course performance isn't great, but it should work everywhere.

PS - I think you cheated, you got a paging answer and an EXCEPT ORDER BY answer.

Jabot

Answer 4

This answer might have been a comment, but the documentation is a but subtle on this point and requires some explanation.

If you look at the syntax definition of the set operations (union, union all, intersect, except), you'll see that they reference "query_expression" and "query specification":

{ <query_specification> | ( <query_expression> ) } 
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }

Logic would suggest that these are select statements in their full glory. But they are not. The full glory of the select statement includes four clauses not included in this: with, for, order by, and option. So, those are not allowed.

That said, I do think the above expression is not correct, because <query_specification> includes into as I read the documentation. The more accurate specification would be the same as for union:

{ <query_specification> | ( <query_expression> ) } 
{ EXCEPT | INTERSECT }
{ <query_expression> }

I don't believe that into would be allowed on both parts of a query that uses a set operation.

Ultimately