Question

IF you want to use an ORDER BY in a query involving set operations, where should you place the ORDER BY?

Answer 1

In SQL, the ORDER BY clause should be positioned at the end of the entire query when working with set operations like UNION, INTERSECT, or EXCEPT, to sort the combined result set.

Step-by-step explanation:

When using an ORDER BY clause in SQL, particularly in queries involving set operations like UNION, INTERSECT, or EXCEPT, the ORDER BY clause must be placed at the very end of the entire query. This is because set operations combine the results of two or more SELECT statements into a single result set. Only once this combined result set has been fully formed can the rows be ordered. Placing the ORDER BY clause elsewhere, such as within individual SELECT statements that are part of the set operation, would result in a syntax error. Here is an example:

• SELECT column1 FROM table1
  UNION
  SELECT column1 FROM table2
  ORDER BY column1;

The ORDER BY clause here applies to the result of the UNION of two SELECT statements, ensuring the combined results are sorted according to 'column1'.