Minus Query

  • khwabekhan

    Valued Member

    Points: 64

    Comments posted to this topic are about the item Minus Query

  • dale_berta

    Ten Centuries

    Points: 1024

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

  • Mighty

    SSCrazy Eights

    Points: 8812

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

  • khwabekhan

    Valued Member

    Points: 64

    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.

     

     

  • ako58

    Hall of Fame

    Points: 3402

    (SELECT FIRSTNAME

    FROM EMPLOYEE

    EXCEPT

    SELECT FIRSTNAME

    FROM PERSON)

    UNION

    (SELECT FIRSTNAME

    FROM PERSON

    EXCEPT

    SELECT FIRSTNAME

    FROM EMPLOYEE)

    ORDER BY FIRSTNAME;

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71854

    Tricksey, tricksey

    Nice question, thanks

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

  • AZ Pete

    Mr or Mrs. 500

    Points: 511

    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 2 months ago by  AZ Pete.

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

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