Minus Query

  • Comments posted to this topic are about the item Minus Query

  • Pretty sure the restriction on ORDER BY is on the UNION, not the EXCEPT and INTERSECT. Can anyone confirm?

  • The order by is not allowed for an EXCEPT, and neither allowed for a UNION.

  • Order by must not be used on the first query whenever you are using except,intersect or minus query.

    But you can use order by in the second query.Order by in the second query is to sort the final result.

     

     

  • (SELECT FIRSTNAME

    FROM EMPLOYEE

    EXCEPT

    SELECT FIRSTNAME

    FROM PERSON)

    UNION

    (SELECT FIRSTNAME

    FROM PERSON

    EXCEPT

    SELECT FIRSTNAME

    FROM EMPLOYEE)

    ORDER BY FIRSTNAME;

  • Tricksey, tricksey

    Nice question, thanks

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • The ORDER BY can only appear after the second query in an EXCEPT or INTERSECTION operator and only reference columns from the left (first) query.

    This would work:

    SELECT E.FIRSTNAME

    FROM @EMPLOYEE E

    EXCEPT

    SELECT P.FIRSTNAME

    FROM @PERSON P

    ORDER BY E.FIRSTNAME

    This would throw an error:

    SELECT E.FIRSTNAME

    FROM @EMPLOYEE E

    EXCEPT

    SELECT P.FIRSTNAME

    FROM @PERSON P

    ORDER BY P.FIRSTNAME

    • This reply was modified 4 years, 5 months ago by  AZ Pete.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply