June 4, 2020 at 12:00 am
Comments posted to this topic are about the item Minus Query
June 4, 2020 at 12:22 pm
Pretty sure the restriction on ORDER BY is on the UNION, not the EXCEPT and INTERSECT. Can anyone confirm?
June 4, 2020 at 2:04 pm
The order by is not allowed for an EXCEPT, and neither allowed for a UNION.
June 4, 2020 at 2:47 pm
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.
June 4, 2020 at 8:04 pm
(SELECT FIRSTNAME
FROM EMPLOYEE
EXCEPT
SELECT FIRSTNAME
FROM PERSON)
UNION
(SELECT FIRSTNAME
FROM PERSON
EXCEPT
SELECT FIRSTNAME
FROM EMPLOYEE)
ORDER BY FIRSTNAME;
June 5, 2020 at 11:17 am
Tricksey, tricksey
Nice question, thanks
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
June 5, 2020 at 6:05 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply