Unions and Sorting
by Vickram H[ Edit ] 2012-07-31 10:33:36
Unions and Sorting:
The ORDER BYclause cannot appear in either of the two SELECTstatements combined by a UNIONoperation. It wouldn't make much sense to sort the two sets of query results anyway, because they are fed directly into the UNIONoperation and are never visible to the user. However, the combinedset of query results produced by the UNION operation can be sorted by specifying an ORDER BYclause after the second SELECT statement.
Since the columns produced by the UNIONoperation are not named, the ORDER BY clause must specify the columns by column number.
List all the products where the price of the product exceeds $2,000 or where more than $30,000 of the product has been ordered in a single order, sorted by manufacturer and
product number.
SELECT MFR_ID, PRODUCT_ID
FROM PRODUCTS
WHERE PRICE > 2000.00
UNION
SELECT DISTINCT MFR, PRODUCT
FROM ORDERS
WHERE AMOUNT > 30000.00
ORDER BY 1, 2
ACI 4100Y
ACI 4100Z
IMM 775C
REI 2A44L
REI 2A44R