CODEWITHSUNDEEP
oracle-databasesql-order-by
ladookie
ladookie

Reputation: 1371

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.

Upvotes: 6

Views: 16512

Answers (3)

case nelson
case nelson

Reputation: 3667

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

Upvotes: 8

Jon Heller
Jon Heller

Reputation: 36902

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.

Upvotes: 2

Jonathan Leffler
Jonathan Leffler

Reputation: 754570

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.

Upvotes: 1

Related Questions