Geek Answers Handbook

How do you order BY in a query using MINUS?

I want ORDER BYa query result MINUS.

My first attempt does not work:

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

How do you do this?

oops: I did ORDER BY table2.fooinstead ORDER BY foo. Now it works.

+5
source share
3 answers

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

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 make a subquery:

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

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

If this does not 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 needed.

+1
source

. , foo :

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

, , adhoc-.

+1

More articles:


All Articles