How do you ORDER BY in a query using MINUS?

I want to ORDER BY the result of a MINUS query.

My first attempt doesn't work:

SELECT *
FROM Table1
MINUS
SELECT *
FROM table2
WHERE table2.foo = 'bar'
ORDER BY foo

How would you do it?

oops: I was doing ORDER BY table2.foo instead of just ORDER BY 开发者_运维知识库foo. Now it works.

However, to answer your question, you can use a with query:

with tmp_minus as (
    SELECT *
    FROM Table1
    MINUS
    SELECT *
    FROM table2
    WHERE table2.foo = 'bar'
) 
select * from tmp_minus 
ORDER BY foo

You should also be able to do a subselect:

select * from (
    SELECT *
    FROM Table1
    MINUS
    SELECT *
    FROM table2
    WHERE table2.foo = 'bar'
) tmp_minus 
ORDER BY foo

You can use the position instead of the column name. Assuming that foo is the first column in the results:

SELECT *
FROM Table1
MINUS
SELECT *
FROM table2
WHERE table2.foo = 'bar'
ORDER BY 1

You normally do not want results to depend on a specific column order, so I would only use this for adhoc queries.

If the MINUS were replaced by UNION, the ORDER BY would apply to the result of the UNION. Are you sure that's not what you get with MINUS?

If it doesn't work directly, then:

SELECT result.*
  FROM (SELECT *
          FROM Table1
        MINUS
        SELECT *
          FROM table2
         WHERE table2.foo = 'bar') AS result
 ORDER BY foo;

However, I think this is unlikely to be necessary.